sql 动态行转列 （2005及以上版本）

表数据：

 

sql：

--pivot方案 sql 2005及以上版本
Declare @sql varchar(8000)
Set @sql=(Select DISTINCT ','+ N'[' +pref_name+N']' FROM dbo.PopTbl FOR XML PATH(''))
Set @sql=STUFF(@sql,1,1,'')
Set @sql='Select * From PopTbl
PIVOT
(
    sum(population) For pref_name in('+@sql+')
) as pvt'
exec(@sql)

go


DECLARE @s NVARCHAR(4000) 
SELECT @s = ISNULL(@s + ',', '') + QUOTENAME(A.pref_name) 
FROM (select distinct pref_name from dbo.PopTbl) as A ---列名不要重复 
Declare @sql NVARCHAR(4000) 
SET @sql=' 
select r.* from 
(select * from dbo.PopTbl ) as t 
pivot 
( 
SUM(t.population) 
for t.pref_name  in ('+@s+') 
) as r' 
EXEC( @sql) 

 

 

 

结果：