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;