Skip to content
Advertisement

Oracle SQL Transforming sql rows into columns grouping by another column

I am facing trouble designing SQL for the below scenario.

My table structure looks like this

TABLE IMSK

id | key | value | group_id
1  | k1  | a1    | g1
2  | k2  | a2    | g1
3  | k3  | a3    | g1
4  | k1  | b1    | g2
5  | k2  | b2    | g2
6  | k3  | b3    | g2

As you can see, I store 3 keys for every group id. Values will be different for each group_id. I need to show this information in a report in the following manner.

k1 | k2 | k3
a1 | a2 | a3
b1 | b2 | b3

Keys will become the column headers and every row will correspond to a group_id

Advertisement

Answer

One method is conditional aggregation:

select group_id,
       max(case when key = 'k1' then value end) as key1,
       max(case when key = 'k2' then value end) as key2,
       max(case when key = 'k3' then value end) as key3
from imsk
group by group_id;
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement