I have the following table:

enter image description here

Then I want to get the following:

enter image description here

I want to achieve this in postgres. What should I do?

  • how can I make this a generic one which would apply to any tables – Farhaan Patel May 21 '18 at 9:24
  • You can create procedure to perform this action automatically. In that procedure You'll obtain all the names of tables in Your database and the field(s) in their primary indexes from INFORMATION_SCHEMA views. See postgresql.org/docs/9.6/static/information-schema.html – Akina May 21 '18 at 9:35

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')
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.