Kush Kush - 1 year ago 100
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?

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download