postgres - pivot query with array values

If the subtitle value is the only "constant" that you wan to separate, you can do:

SELECT * FROM crosstab
(
  'SELECT content.id,name,array_to_string(array_agg(value),'','')::character varying FROM content    inner join 
    (
    select * from fields where fields.name = ''subtitle''  
        union all  
    select * from fields where fields.name <> ''subtitle'' 
    ) fields_ordered 
   on fields_ordered.id_content = content.id   group by content.id,name'
)
AS
(
       id integer,
       content_name character varying,
       tags character varying
);

You can use array_agg, e.g.

SELECT id_content, array_agg(value)
FROM fields
WHERE name = 'tags'
GROUP BY id_content

If you need the subtitle, too, use a self-join. I have a subselect to cope with subtitles that don't have any tags without returning arrays filled with NULLs, i.e. {NULL}.

SELECT f1.id_content, f1.value, f2.value
FROM fields f1
LEFT JOIN (
    SELECT id_content, array_agg(value) AS value
    FROM fields
    WHERE name = 'tags'
    GROUP BY id_content
) f2 ON (f1.id_content = f2.id_content)
WHERE f1.name = 'subtitle';

See http://www.postgresql.org/docs/9.3/static/functions-aggregate.html for details.

If you have access to the tablefunc module, another option is to use crosstab as pointed out by Houari. You can make it return arrays and non-arrays with something like this:

SELECT id_content, unnest(subtitle), tags
FROM crosstab('
    SELECT id_content, name, array_agg(value)
    FROM fields
    GROUP BY id_content, name
    ORDER BY 1, 2
') AS ct(id_content integer, subtitle text[], tags text[]);

However, crosstab requires that the values always appear in the same order. For instance, if the first group (with the same id_content) doesn't have a subtitle and only has tags, the tags will be unnested and will appear in the same column with the subtitles.

See also http://www.postgresql.org/docs/9.3/static/tablefunc.html