T:SQL：从行中选择值作为列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13103114/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T:SQL: select values from rows as columns
提问by Alaa
I have a table for Profiles stores profile properties values in row style, ex:
我有一个配置文件表以行样式存储配置文件属性值，例如：
[ProfileID] [PropertyDefinitionID] [PropertyValue]
1 6 Jone
1 7 Smith
1 8 Mr
1 3 50000
and another table for property definitions :
和另一个属性定义表：
[PropertyDefinitionID] [PropertyName]
6 FirstName
7 LastName
8 Prefix
3 Salary
How to use PIVOTor any other way to show it in this way:
如何使用PIVOT或以任何其他方式以这种方式显示它：
[ProfileID] [FirstName] [LastName] [Salary]
1 Jone Smith 5000
回答by Roman Pekar
It's easy to do this without PIVOTkeyword, just by grouping
无需PIVOT关键字即可轻松完成此操作，只需分组即可
select
P.ProfileID,
min(case when PD.PropertyName = 'FirstName' then P.PropertyValue else null end) as FirstName,
min(case when PD.PropertyName = 'LastName' then P.PropertyValue else null end) as LastName,
min(case when PD.PropertyName = 'Salary' then P.PropertyValue else null end) as Salary
from Profiles as P
left outer join PropertyDefinitions as PD on PD.PropertyDefinitionID = P.PropertyDefinitionID
group by P.ProfileID
you can also do this with PIVOTkeyword
你也可以用PIVOT关键字来做到这一点
select
*
from
(
select P.ProfileID, P.PropertyValue, PD.PropertyName
from Profiles as P
left outer join PropertyDefinitions as PD on PD.PropertyDefinitionID = P.PropertyDefinitionID
) as P
pivot
(
min(P.PropertyValue)
for P.PropertyName in ([FirstName], [LastName], [Salary])
) as PIV
UPDATE: For dynamic number of properties - take a look at Increment value in SQL SELECT statement
更新：对于动态数量的属性 - 查看SQL SELECT 语句中的增量值
回答by Taryn
It looks like you might have an unknown number of PropertyName'sthat you need to turn into columns. If that is the case, then you can use dynamic sql to generate the result:
看起来您可能有未知数量的PropertyName's需要转换为列。如果是这种情况，那么您可以使用动态 sql 生成结果：
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(PropertyName)
from propertydefinitions
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT profileid, ' + @cols + ' from
(
select p.profileid,
p.propertyvalue,
d.propertyname
from profiles p
left join propertydefinitions d
on p.PropertyDefinitionID = d.PropertyDefinitionID
) x
pivot
(
max(propertyvalue)
for propertyname in (' + @cols + ')
) p '
execute(@query)
See SQL Fiddle with Demo.
