CODEWITHSUNDEEP
sqlpostgresql
n1000
n1000

Reputation: 5314

Postgresql: Split columns into rows

I have a table like this:

gid | cat1 | cat2 | some_value
-------------------------------
 1  |  A   | 100  |    I 
 2  |  B   | 200  |    II
 3  |  C   | 300  |    III
 4  |  D   | 400  |    IV

I would like to convert this table into something like this, i.e. convert a column value into a row value:

gid | category | value | some_value
-----------------------------------
 1  |   cat1   |   A   |     I
 1  |   cat2   |  100  |     I
 2  |   cat1   |   B   |     II
 2  |   cat2   |  200  |     II
... |   ...    |  ...  |     ...

Is this possible in Postgresql?

Upvotes: 2

Views: 560

Answers (1)

Taher Rahgooy
Taher Rahgooy

Reputation: 6696

Use Union:

Select gid, 'cat1' as category, cat1 as value, some_value from t
union all
Select gid, 'cat2' as category, cast(cat2 as varchar(100)) as value, some_value from t
order by gid

here is a fiddle for it.

Upvotes: 2

Related Questions