SQLServer pivot动态行转列

--sql2005及以上版本

多行按一列转多列
Declare @sql varchar(max)
Set @sql=(Select DISTINCT ','+tid From amrbill  FOR XML PATH(''))
Set @sql=STUFF(@sql,1,1,'')
Set @sql='Select * From amrbill
PIVOT
(
    SUM(total) For tid  in('+@sql+')
) as pvt'
exec(@sql)

 

多行按两列转多列

实现方式：在外面套一层查询语句 如操作多列以此类推 

Declare @sql varchar(max), @sql2 varchar(max)
Set @sql=(Select DISTINCT ','+tid2 From amrbill FOR XML PATH(''))
Set @sql=STUFF(@sql,1,1,'')

Set @sql2=(Select DISTINCT ','+tid3 From amrbill FOR XML PATH(''))
Set @sql2=STUFF(@sql2,1,1,'')

Set @sql='select * from ( Select * From amrbill
PIVOT
(
SUM(total) For tid2 in('+@sql+')
) as pvt ) t
PIVOT
(
SUM(total_doub) For tid3 in('+@sql2+')
) as pvt2'
exec(@sql)