转换具有"id，attribute，value"的表； "id，attribute1，attribute2，..."列 [英] Convert tables with "id,attribute,value" columns to "id,attribute1,attribute2,..."

问题描述

我在数据库中有一个表，如下所示:

I've got a table in database that looks like this:

CustomerId    Attribute    Value
================================
30            Name         John
30            Surname      Smith
30            Phone        [phone]

要充分利用数据，我需要使其看起来更自然.像这样:

to make some use of the data I need to make it look sth. like this:

CustomerId    Name    Surname    Phone
======================================
30            John    Smith      555123456

我知道我可以创建将对我有用的存储过程或表值函数，但是我想知道什么是最好的选择?也许我只能用一些聪明的sql select查询来做到这一点?

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?

推荐答案

如果是SQL Server 2005或更高版本，则可以使用PIVOT:

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

或不使用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

这篇关于转换具有"id，attribute，value"的表； "id，attribute1，attribute2，..."列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！