--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)