sql - postgres - pivot query with array values -

sql - postgres - pivot query with array values -

suppose have table:

content +----+---------+ | id | title | +----+---------+ | 1 | lorem | +----|---------|

and one:

fields +----+------------+----------+-----------+ | id | id_content | name | value | +----+------------+----------+-----------+ | 1 | 1 | subtitle | ipsum | +----+------------+----------+-----------| | 2 | 1 | tags | tag1 | +----+------------+----------+-----------| | 3 | 1 | tags | tag2 | +----+------------+----------+-----------| | 4 | 1 | tags | tag3 | +----+------------+----------+-----------|

the thing is: want query content, transforming rows "fields" columns, having like:

+----+-------+----------+---------------------+ | id | title | subtitle | tags | +----+-------+----------+---------------------+ | 1 | lorem | ipsum | [tag1,tag2,tag3] | +----+-------+----------+---------------------|

also, subtitle , tags examples. can have many fields desired, them beingness array or not.

but haven't found way convert repeated "name" values array, more without transforming "subtitle" array well. if that's not possible, "subtitle" turn array , alter later on code, needed @ to the lowest degree grouping somehow. ideas?

if subtitle value "constant" wan separate, can do:

select * crosstab ( 'select content.id,name,array_to_string(array_agg(value),'','')::character varying content inner bring together ( select * fields fields.name = ''subtitle'' union select * fields fields.name <> ''subtitle'' ) fields_ordered on fields_ordered.id_content = content.id grouping content.id,name' ) ( id integer, content_name character varying, tags character varying );

sql postgresql pivot