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

效果如图,把同一个 code, 按 cate 列分为 Actual 和 Budget 两行,再把mode 每种类型转换成 列名 ,主要用到了 max 函数,很实用

SQL- 行转列,多行转多列 - 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
上一篇:CentOS 6.5的安装详解


下一篇:Spring MVC中注解 @ModelAttribute