I need to unpivot a table in PostgreSQL. The first dataset is what I have, the second is what I want:

enter image description here

Below is my schema with sample dataset:


My understanding is that you may have to use JSON functions to accomplish this in PostgreSQL, as it doesn't have an unpivot function as some other SQL variants do. I'm open to other solutions, though.

  • 1
    If I'm interpreting your info correctly, your first data set is what you have, and your second is what you want. There's no JSON data in either - why do you want to unpivot using JSON, rather than simply unpivot? What do you have in mind when you say "unpivot using JSON"? – RDFozz Jun 29 '18 at 16:08

Unpivot can be done using an array and unnest:

select s.state, s.city, 'item'||t.i as items, t.item
from state1 s
  cross join lateral unnest(array[item1, item2, item3]) with ordinality as t(item,i);

Online example: http://rextester.com/VPDDN96018

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