1

I would like to know is there any way to do unpivot (turn columns to rows) data in PostgreSQL.

For example, if I have a table like:

ID   Name    Age
1    Alice    16
2    Bob      21
3    Carl     18

I want to get an output like

ID   Column_Name Column_Value
1    Name        Alice
1    Age         16
2    Name        Bob
2    Age         21
3    Name        Carl
3    Age         18

I know I could do it like (sql fiddle with data to try it):

select
   U.ID,
   unnest(array['Name', 'Age']) as Column_Name,
   unnest(array[U.Name, U.Age::text]) as Column_Value
from Users as U

But is there any way I could do it for all columns in table without explicitly specifying column names? For example, for SQL Server I know of at least 2 ways to do it - dynamic SQL or turn data into xml and parse xml - SQL Server : Columns to Rows. May be there's some xml trick in PostgreSQL too?

Community
  • 1
  • 1
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197

1 Answers1

1

With hstore extension:

SELECT id, skeys(hstore(users)) AS column, svals(hstore(users)) AS value FROM users;
rkhayrov
  • 10,040
  • 2
  • 35
  • 40