0

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:

http://www.sqlfiddle.com/#!17/3f45c/1

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.

RDFozz
  • 11,731
  • 4
  • 25
  • 38
sqldbforum
  • 11
  • 1
  • 2

1 Answers1

2

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