Kush Kush - 4 months ago 57
SQL Question

SQL pivot-like records arrangement

I have following table which carries records.

ID header value
1 firstname James
1 lastname Tulan
2 firstname Berty
2 lastname O-Nelly
3 firstname Ana
3 lastname Santos

I need to display the records as follows

id firstname Lastname
1 James Tulan
2 Berty O-Nelly
3 Ana Santos

I tried to use SQL PIVOT function. It didn't work properly. Anyone encountered the same?


Assuming the ids in the first table do identify the pairs, you can just do aggregation:

select id,
       max(case when header = 'firstname' then value end) as firstname,
       max(case when header = 'lastname' then value end) as lastname
from t
group by id;