使用 ROW_NUMBER() 的基本 PIVOT 将为您做一些事情:
SELECT [Developer],
[Designer],
[Coder]
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY (SELECT NULL)) RN
FROM #temp
) as t
PIVOT (
MAX(Name) FOR Occupation IN ([Developer],[Designer],[Coder])
) as pvt
Output:
Developer Designer Coder
A X Y
B NULL Z
如果数量Occupation
s 可能会有所不同,那么您需要动态 SQL:
DECLARE @columns nvarchar(max),
@sql nvarchar(max)
SELECT @columns = (
SELECT DISTINCT ','+QUOTENAME(Occupation)
FROM #temp
FOR XML PATH('')
)
SELECT @sql = N'
SELECT '+STUFF(@columns,1,1,'')+'
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY (SELECT NULL)) RN
FROM #temp
) as t
PIVOT (
MAX(Name) FOR Occupation IN ('+STUFF(@columns,1,1,'')+')
) as pvt'
EXEC sp_executesql @sql
Note:我用过ORDER BY (SELECT NULL)
只是为了获得一些随机排序。为此,最好使用一些实际字段。