I am unable to convert multiple columns into a single column keeping remaining columns intact
I have the following table table1
Name Category1 Category2 Category3 ..... Tag
======= =========== =========== =========== ========
Jason 5 6 4 senior
Walter 3 7 10 junior
.
.
.
How do I merge all categories into a single column called category_mix?
Basically, I want to use the data from table1 to populate another table, table2 such that it gets populated in this manner
Name category_mix Tag
======= ============== =======
Jason 5 senior
Jason 6 senior
Jason 4 senior
Walter 3 junior
Walter 7 junior
Walter 10 junior
How do I go about doing this in Postgres?
You can use a values clause to achieve the unpivot:
select t.name, c.category_mix, t.tag
from table1 t
cross join lateral (
values (category1), (category2), (category3)
) as c(category_mix);