Convert tables with "id,attribute,value" columns to "id,attribute1,attribute2,..."

pivot, sql-server, t-sql

Solution

If this is SQL Server 2005 or later, you can use `PIVOT`:

with Customers (CustomerId, Attribute, Value)
as
(
    select 30, 'Name', 'John'
    union
    select 30, 'Surname', 'Smith'
    union
    select 30, 'Phone', '551123456'
    union
    select 40, 'Name', 'Mary'
    union
    select 40, 'Surname', 'Johnson'
    union
    select 40, 'Phone', '9991111234'
)
select CustomerId, Name, Surname, Phone
from Customers
pivot
(
    min(Value)
    for Attribute in (Name, Surname, Phone)
) as PivotTable

Or a solution that does not use `PIVOT`:

/* with cte defined as above */
select CustomerId, 
    max(case Attribute when 'Name' then Value else '' end) Name,
    max(case Attribute when 'Surname' then Value else '' end) Surname,
    max(case Attribute when 'Phone' then Value else '' end) Phone
from Customers
group by CustomerId

Problem

I've got a table in database that looks like this: ``` CustomerId Attribute Value ================================ 30 Name John 30 Surname Smith 30 Phone 555123456 ``` to make some use of the data I need to make it look sth. like this: ``` CustomerId Name Surname Phone ====================================== 30 John Smith 555123456 ``` I know I can create stored procedure or table-valued function that will do it for me, but I'm wondering what would be the best choice here? Maybe I can do it with some clever sql select query only?

Original source