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.
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;
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:
