CODEWITHSUNDEEP
sqlpostgresqlpivotcrosstab
Alex Zaitsev
Alex Zaitsev

Reputation: 701

Convert rows to columns by same column value in Postgres

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.

Upvotes: 1

Views: 12897

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656311

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:

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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;

Upvotes: 8

Related Questions