SQL- 行转列，多行转多列 - max 函数

效果如图，把同一个 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            '',N'深圳','','Actual','','','BLK',10,1,'3.57%'

 union all select '',N'深圳','','Actual','','','V15',20,2,'7.14%'

 union all select '',N'深圳','','Actual','','','V5',30,3,'10.71%'

 union all select '',N'深圳','','Actual','','','V0',40,4,'14.29%'

 union all select '',N'深圳','','Actual','','','V20',50,5,'17.86%'

 union all select '',N'深圳','','Actual','','','V10',60,6,'21.43%'

 union all select '',N'深圳','','Actual','','','V25',70,7,'25.00%'

 union all select '',N'深圳','','Budget','','','BLK',10,1,'3.57'

 union all select '',N'深圳','','Budget','','','V15',20,2,'7.14%'

 union all select '',N'深圳','','Budget','','','V5',30,3,'10.71%'

 union all select '',N'深圳','','Budget','','','V0',40,4,'14.29%'

 union all select '',N'深圳','','Budget','','','V20',50,5,'17.86%'

 union all select '',N'深圳','','Budget','','','V10',60,6,'21.43%'

 union all select '',N'深圳','','Budget','','','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