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