Convert rows to columns by same column value in Postgres

Question

I have a table like:

id     name    value
--------------------
1      x       100
1      y       200
1      z       300
2      x       10
2      y       abc
2      z       001
3      x       1
...
--------------------

and I need to transform it into something like that:

id    x     y     z
---------------------
1     100   200   300
2     10    abc   001
3     1     ...
---------------------

Names are determined. I could make multiple joins but I'm looking for a more elegant solution.

Answer 1

Use conditional aggregation which in Postgres uses the filter syntax:

select id,
       max(value) filter (where name = 'x') as x,
       max(value) filter (where name = 'y') as y,
       max(value) filter (where name = 'z') as z
from t
group by id;

Answer 2

The additional module tablefunc provides variants of the crosstab() function, which is typically fastest:

SELECT *
FROM   crosstab(
   'SELECT id, name, value
    FROM   tbl
    ORDER  BY 1, 2'
   ) AS ct (id int, x text, y text, z text);

You seem to have a mix of numbers and strings in your value, so I chose text as output.

See:

• PostgreSQL Crosstab Query