If I have a query like this
SELECT * FROM table1
I get a result something like this:
How can I write a query from the same table that returns me something like this:
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
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.
