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?
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
Sounds like you need to PIVOT. You should read the following article to learn how to do that:
PIVOT functionality in SQL Server
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
