string_agg show info in columns

Question

I'm using the function string_agg to show together some values of the same ID, and it works well because it shows the info concatenated:

SELECT string_agg(table1.value , '#BR#') as "values"
        FROM table1 WHERE id=1

id | values
--------------
1  | v1#BR#v2#BR#v3#BR#v4#BR#v5#BR#v6

Is there a way I can show the info on separated columns? Something like this:

id | values | values | values | values | values | values |
----------------------------------------------------------
1  | v1     | v2     | v3     | v4     | v5     | v6

so you want to separate values and make new field not as 1 field? — dwir182, Sep 20 '18 at 01:10
https://stackoverflow.com/search?tab=votes&q=postgresql%20pivot — Nick, Sep 20 '18 at 01:15

score 1 · Answer 1 · 2018-09-20T14:48:08.290

If you can define a sensible upper limit for the number of columns you could use something like this:

select id, 
       v[1] as value_1, 
       v[2] as value_2, 
       v[3] as value_3, 
       v[4] as value_4, 
       v[5] as value_5
from (
  select id, array_agg(value) as v
  from table1
  where id = 1
) t;

If you expect more columns, just add more expressions to the outer SELECT list

Note that accessing array elements that don't exists will not result in an error, it will only yield NULL. So you can create any number of columns you like even if you know that there won't be values for all of them all the time.

string_agg show info in columns

1 Answers1