Postgresql: Split columns into rows

Question

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?

Taher Rahgooy · Accepted Answer · 2015-08-30 19:38:36Z

2

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.

edited Aug 30, 2015 at 19:38

answered Aug 29, 2015 at 8:14

Taher Rahgooy

6,7163 gold badges22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

n1000 Over a year ago

Thank you very much! However, I get column "gid" does not exist for the first line. Any ideas?

Taher Rahgooy Over a year ago

do you have column gid in your table? did you changed table name from t to your actual table name?

n1000 Over a year ago

Yes and yes - I actually built the table from my example. If I add from t in the first row it works, but I get UNION types character varying and integer cannot be matched in line 3

Taher Rahgooy Over a year ago

Oh, the cast should be for cat2, I changed it

Collectives™ on Stack Overflow

Postgresql: Split columns into rows

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related