PostGIS: Extract intersected areas with their related column values

Question

I have inserted a MultiPolygon table in PostGIS with the following columns: id, e_id , p_id, com, geom

Assume that I have 4 polygons with overlapped areas with the following values:

id  com  geom
1   1_1  geom1
2   1_2  geom2
3   2_1  geom3
4   2_2  geom4

Is it possible to extract only the intersected areas and concatenate their related values ? The result that I want to succeed is a table or shapefile like the following:

id  com      geom
1   1_1_2_1  geom1
2   2_1_2_2  geom2

If the polygon with id: 1 intersects with polygon with id: 3 and polygon with id: 3 intersects with polygon with id: 4

Until now I can extract only the geometries of intersected areas in a column with the following SQL code:

SELECT ST_INTERSECTION(a.geom, b.geom)
FROM mytable a, mytable b
WHERE a.ID < b.ID
AND ST_INTERSECTS(a.geom, b.geom);

Any suggestions?

UPDATE

with @JGH helpful answer, I managed to develop more my code and now I can concatenate the com values from two intersected areas. The problem that remains the same is the id column that contains duplicate values. Is it possible to create a unique id column in the query ?

I tried SEQUENCE command in order to create a unique id column:

CREATE SEQUENCE serial START 1;
SELECT nextval('serial'),
a.com || '_' || b.com as com,
ST_INTERSECTION(a.geom, b.geom) as geom 
FROM mytable a, mytable b
WHERE a.ID < b.ID
AND ST_INTERSECTS(a.geom, b.geom);

But it's not the solution that I wanted because, every time that I execute the code the id numbers increment.

Answer 1

You can add and manipulate fields in your query

SELECT 
  a.com || '_' || b.com as com
  ST_INTERSECTION(a.geom, b.geom) as geom
FROM mytable a, mytable b
WHERE a.ID < b.ID
AND ST_INTERSECTS(a.geom, b.geom);