CODEWITHSUNDEEP
sqlpostgresqlpivot
Braven
Braven

Reputation: 553

Transform rows to columns SQL Query

If I have a query like this

SELECT * FROM table1

I get a result something like this:

enter image description here

How can I write a query from the same table that returns me something like this:

enter image description here

value_name has to turn into columns and the value column has to turn into its values.

Also notice that the ids are repeated and its description is always the same one.

I'm working with PostgresQL

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you know the values in advance, you can use conditional aggregation:

select id, description,
       max(value) filter (where value_name = 'FE') as fe,
       max(value) filter (where value_name = 'H2O') as h2o,
       max(value) filter (where value_name = 'N') as n
from t
group by id, description;

If you don't know the names, then you cannot accomplish this with a single SQL query. You need to use dynamic SQL or use an alternate data representation such as JSON.

Upvotes: 2

Related Questions