效果如图，把同一个 code， 按 cate 列分为 Actual 和 Budget 两行，再把mode 每种类型转换成 列名 ，主要用到了 max 函数，很实用
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#t')) drop table #t create table #t( code varchar(10), cname nvarchar(30), fyear varchar(30), cate varchar(10), mt numeric(18,4), amt numeric(18,2), mode nvarchar(20), mo_mt numeric(18,4), mo_avgfee numeric(18,2), mo_rate nvarchar(20) )insert into #t select '400',N'深圳','2017','Actual','280','1400','BLK',10,1,'3.57%' union all select '400',N'深圳','2017','Actual','280','1400','V15',20,2,'7.14%' union all select '400',N'深圳','2017','Actual','280','1400','V5',30,3,'10.71%' union all select '400',N'深圳','2017','Actual','280','1400','V0',40,4,'14.29%' union all select '400',N'深圳','2017','Actual','280','1400','V20',50,5,'17.86%' union all select '400',N'深圳','2017','Actual','280','1400','V10',60,6,'21.43%' union all select '400',N'深圳','2017','Actual','280','1400','V25',70,7,'25.00%' union all select '400',N'深圳','2018','Budget','280','0','BLK',10,1,'3.57' union all select '400',N'深圳','2018','Budget','280','0','V15',20,2,'7.14%' union all select '400',N'深圳','2018','Budget','280','0','V5',30,3,'10.71%' union all select '400',N'深圳','2018','Budget','280','0','V0',40,4,'14.29%' union all select '400',N'深圳','2018','Budget','280','0','V20',50,5,'17.86%' union all select '400',N'深圳','2018','Budget','280','0','V10',60,6,'21.43%' union all select '400',N'深圳','2018','Budget','280','0','V25',70,7,'25.00%' select * from #t --增加一个强制mode 排序，比如从 vo v1 v2 依次排序 if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#sort')) drop table #sort create Table #sort ( mode varchar(10), ) insert into #sort select distinct mode FROM #t GROUP BY mode order by mode -- select * from #sort declare @sql nvarchar(max) --声明一个变量 SET @sql = 'SELECT code ' +',cname '+',fyear '+',cate ' --+ N'''类别''' +',isnull(mt,0) mt' -- + N'''吨数''' +',isnull(amt,0) amt' -- + N'''金额''' select @sql = @sql + ' , max(case mode when ''' + mode+ ''' then mo_rate else '''' end) [' + mode+ ']' + ' , max(case mode when ''' + mode+ ''' then mo_avgfee else 0 end) [' + mode+ '_unit]'from (select mode FROM #sort ) as a --print @sql set @sql = @sql + ' from #t group by code, cname,fyear,cate,mt,amt order by code,fyear ' print @sql exec(@sql) --执行该sql