I have the following table:
Then I want to get the following:
I want to achieve this in postgres. What should I do?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Tabular data can be easily unpivoted with the help of JSON functions.
The query below will work, assuming
tablename is replaced by your table name, and
primary_key is the name of the pivot column. The other columns don't have to be specified, they're obtained automatically by
json_each_text applied to
row_to_json applied to all rows.
SELECT primary_key, key, value FROM (select row_to_json(t.*) AS line, primary_key FROM tablename AS t) AS r JOIN LATERAL json_each_text(r.line) ON (key <> 'primary_key')