我写了这个查询：
- 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)
