将表1转化成表2：
表1
表2
得到表2的结果，需要经过多次pivot转换，再经union连接到一起，代码如下：
select id, type,sum([]) [],sum([]) [],sum([]) [],sum([]) [] from
(
select 'a' as type, * from Table_1
pivot(sum(a) for p in([],[],[],[])) as a
union all
select 'b' as type,* from Table_1
pivot(sum(b) for p in([],[],[],[])) as b
union all
select 'c' as type,* from Table_1
pivot(sum(c) for p in([],[],[],[])) as c
union all
select 'd' as type,* from Table_1
pivot(sum(d) for p in([],[],[],[])) as d
) t1
group by id,type
order by id,type
此时代码看起来比较多，如果需要n多次pivot转换，代码过于繁多。
此时，可通过定义一个变量，以拼字符串的形式，来代替繁多的代码：
declare @str varchar(8000)
set @str = ''
select @str = @str + ' SELECT '''+ NAME + ''' AS TYPE,* FROM Table_1 pivot(SUM('+ NAME +')
for p in ([1],[2],[3],[4])) as '+ NAME +' union ALL '
from syscolumns
where object_id('Table_1') = id AND NAME <> 'P' AND NAME <> 'ID'
select @str = left(@str,len(@str)-len('union ALL'))
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'
exec (@str)
两种方法得出的结果是一样的，只是后者代码更为简洁。