CODEWITHSUNDEEP
sqldatabasepostgresqlaggregate-functions
Anton Bondar
Anton Bondar

Reputation: 429

Postgres array_agg each value at new string within one row

I have such a query

SELECT group_id, array_agg(element_id) FROM table
GROUP BY group_id;

As a result I have something like that:

    group_id | array_agg

    106      | {2147,2138,2144}
    107      | {2132,2510,2139}

What query should be written, so result may be depicted in this way: 

        group_id | array_agg

        106      | {2147
                 |  2138
                 |  2144}
        107      | {2132
                 | 2510
                 | 2139}

Upvotes: 2

Views: 2575

Answers (1)

klin
klin

Reputation: 121474

Basically one should format the output in a client app, however you can use string_agg() with a new-line character:

select group_id, string_agg(element_id::text, e'\n') 
from my_table
group by group_id;

 group_id | string_agg 
----------+------------
      106 | 2147      +
          | 2138      +
          | 2144
      107 | 2132      +
          | 2510      +
          | 2139
(2 rows)

Upvotes: 2

Related Questions