转换具有"id,attribute,value"的表; "id,attribute1,attribute2,..."列 [英] Convert tables with "id,attribute,value" columns to "id,attribute1,attribute2,..."
本文介绍了转换具有"id,attribute,value"的表; "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 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
我知道我可以创建将对我有用的存储过程或表值函数,但是我想知道什么是最好的选择?也许我只能用一些聪明的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屋!
查看全文