更新时间:2023-01-28 18:10:08
尝试以下方法:
SELECT *
FROM (SELECT Item, attribute, value FROM MyTable) AS t
PIVOT
(
MAX(value)
FOR attribute IN([Quality], [Color])
) AS p;
输出:
╔═══════╦═════════╦═══════╗
║ ITEM ║ QUALITY ║ COLOR ║
╠═══════╬═════════╬═══════╣
║ item1 ║ A ║ Red ║
║ item2 ║ B ║ Black ║
╚═══════╩═════════╩═══════╝
如果您不知道attribute
的具体值,也可以使用此动态查询:
You can also use this dynamic query if you don't know the specific value of attribute
:
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(attribute)
from MyTable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT Item,' + @cols + '
from
(
Select Item, attribute , value
from MyTable
) dta
pivot
(
MAX(Value)
for attribute in (' + @cols + ')
) pvt '
execute(@query);