表数据:
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)
结果: