我写了这个查询：
SELECT s,[1] AS a1,[2] AS a2,[3] AS a3,[4] AS a4 FROM (SELECT grade,aid,s FROM m) p PIVOT ( SUM(grade) FOR aid IN ([1],[2],[3],[4]) ) AS pvt ORDER BY pvt.s;
返回结果：
s a1 a2 a3 a4 1 25 69 95 56 2 27 99 16 87 . . . . 99 98 12 34 76
这正是我想要的结果.我的问题是“援助”中并不总是有四个不同的值.是否可以重写此查询(或使用存储过程),以便’a *’列的数量取决于’aid’中有多少个不同的值？
解决方法
您需要使用动态数据透视表来获取所需的列列表.这将首先检索列列表,然后转动该列表.与此类似的东西：
DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(aid)
FROM m
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)'),1,'')
set @query = 'SELECT s,' + @cols + ' from
(
select grade,s
from m
) x
pivot
(
sum(grade)
for aid in (' + @cols + ')
) p
ORDER BY p.s'
execute(@query)
