SQLServer-⾏列转换⾏转列，多⾏转多列-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'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  '
exists的用法print@sql
exec(@sql)  --执⾏该sql