我在行样式中有一个Profiles store配置文件属性值的表,例如：
[ProfileID] [PropertyDefinitionID] [PropertyValue] 1 6 Jone 1 7 Smith 1 8 Mr 1 3 50000
和另一个属性定义表：
[PropertyDefinitionID] [PropertyName] 6 FirstName 7 LastName 8 Prefix 3 Salary
如何使用PIVOT或任何其他方式以这种方式显示它：
[ProfileID] [FirstName] [LastName] [Salary] 1 Jone Smith 5000
解决方法
没有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
您也可以使用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
更新：对于动态数量的属性 – 请查看Increment value in SQL SELECT statement
