sql server多重行列转置的优化

将表1转化成表2：

表1

表2

得到表2的结果，需要经过多次pivot转换，再经union连接到一起，代码如下：

 1 select id, type,sum([1]) [1],sum([2]) [2],sum([3]) [3],sum([4]) [4] from
 2 (
 3     select 'a' as type, * from   Table_1
 4     pivot(sum(a)  for p in([1],[2],[3],[4])) as a 
 5 union all
 6     select 'b' as type,* from   Table_1
 7     pivot(sum(b)  for p in([1],[2],[3],[4])) as b
 8 union all
 9     select 'c'  as type,* from   Table_1
10     pivot(sum(c)  for p in([1],[2],[3],[4])) as c
11 union all
12     select 'd'  as type,* from   Table_1
13     pivot(sum(d)  for p in([1],[2],[3],[4])) as d
14 ) t1
15 group by id,type
16 order by id,type

View Code

此时代码看起来比较多，如果需要n多次pivot转换，代码过于繁多。

此时，可通过定义一个变量，以拼字符串的形式，来代替繁多的代码：

1 declare @str varchar(8000)  
2 set @str = ''
3 select  @str = @str + ' SELECT '''+ NAME + ''' AS TYPE,* FROM Table_1 pivot(SUM('+ NAME +') 
4         for p in ([1],[2],[3],[4])) as '+ NAME +' union ALL ' 
5         from syscolumns 
6         where object_id('Table_1') = id AND NAME <> 'P' AND NAME <> 'ID'
7 select @str = left(@str,len(@str)-len('union ALL'))
8 select @str ='select id, type,sum([1]) [1],sum([2]) [2],sum([3]) [3],sum([4]) [4] from ('+ @str +') t1 group by id,type order by id,type'
9 exec (@str)

View Code

两种方法得出的结果是一样的，只是后者代码更为简洁。