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.

score 3 · Accepted Answer · answered Jul 24 '20 at 16:34

3

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;

answered Jul 24 '20 at 16:34

Gordon Linoff

1,242,037
58
646
786

1

is it possible to make it dynamic? for example for bigger input the entire alphabet (a..z). without writing 26 rows max(value )... ? @gordon-linoff – Ilia Oct 03 '22 at 14:15
@ilia … You could aggregate by the first letter (and id) – Gordon Linoff Oct 05 '22 at 19:27

score 1 · Answer 2 · answered Jul 24 '20 at 16:45

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

Convert rows to columns by same column value in Postgres

2 Answers2