transpose columns to rows - postgres

Question

I need to transpose columns to rows in postgreSQL. Below is the requirement. Any help is appreciated.

Source table/data :

id   class-1-male class-1-female class-2-male  class-2-female class-3-male  class-3-female<br> 
1      1                1                11              7          0                9<br> 
2      11              31                6              7          40               92<br> 
3      15              31                8              37         30               91<br> 
4      11              13                50             17         10               19<br>

I want data in below format:

id   class-type male female<br> 
1     class-1    1     1<br> 
2     class-1    11    31<br> 
3     class-1    15    31<br> 
4     class-1    11    13<br> 
1     class-2    11    7<br> 
2     class-2    6     7<br> 
3     class-2    8     37<br> 
4     class-2    50    17<br> 
1     class-3    0     9<br> 
2     class-3    40    92<br> 
3     class-3    30    91<br> 
4     class-3    10    19<br>

THanks Jitendra

Denis de Bernardy · Accepted Answer · 2013-11-07 09:45:03Z

1

A union is probably what you're looking for:

select id,
       'class1'::text as "class-type",
       "class-1-male" as male,
       "class-1-female" as female
from data
union all
select id,
       'class2'::text as "class-type",
       "class-2-male" as male,
       "class-2-female" as female
from data
union all
…

answered Nov 7, 2013 at 9:45

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

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

1 Comment

Denis de Bernardy Over a year ago

@Lotus92: that one is the other way around. Also, avoid url shorteners on this site.

Collectives™ on Stack Overflow

transpose columns to rows - postgres

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related