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

Question

Hello,

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?

Answer 1

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

Answer 2

Sounds like you need to PIVOT. You should read the following article to learn how to do that:

PIVOT functionality in SQL Server

Answer 3

Given the following scenario:

create table vertical(
CustomerID int, 
attribute varchar(255),
value varchar(255)
)

create table horizontal(
CustomerID int, 
name varchar(255),
surname varchar(255),
phone varchar(255)
)

insert into vertical
    select 30, 'Name', 'John'
insert into vertical
    select 30, 'Surname', 'Smith'
insert into vertical
    select 30, 'Phone', '551123456'
insert into vertical
    select 40, 'Name', 'Mary'
insert into vertical
    select 40, 'Surname', 'Johnson'
insert into vertical
    select 40, 'Phone', '9991111234'
insert into vertical
    select 50, 'Surname', 'Manuel'
insert into vertical
    select 50, 'Phone', '423'

running this insert

insert into horizontal (CustomerID, name, surname, phone)
select c.CustomerID, n.value as name, s.value as surname, ph.value as phone 
from 
 (select distinct CustomerID from vertical) as c
left join vertical n
    on c.CustomerID = n.CustomerID and n.attribute = 'name'
left join vertical s
    on c.CustomerID = s.CustomerID and s.attribute = 'Surname'
left join vertical ph
    on c.CustomerID = ph.CustomerID and ph.attribute = 'Phone'

shall output this:

ID  Name    Surname Phone
--  ----    ------- -----
30  John    Smith   551123456
40  Mary    Johnson 9991111234
50  NULL    Manuel  423

Where:

from: select distinct CustomerID from vertical brings all customers that have at least one attribute.

each left join returns the corresponding value for each attribute.

left join was used to ensure that the customer row will be inserted even if the customer is missing some attributes in which case NULL will be inserted.

source table named vertical is the one with the attribute values

target table named horizontal is your desired output