竞争无处不在,青春永不言败!专业撸代码,副业修bug

Talk is cheap , show me the code!



行列转换

一、MSSQL2005之前的版本行列转换case when+(sum or max)这类聚合函数
/*MSSQL2005版本之前行列转换*/
;with ScoreTab(name,subject,score) as
(
 select 'Tom','Chinese',80
 union all
 select 'Frank','Chinese',90
 union all
 select 'Jerry','Chinese',70
 union all
 select 'Tom','English',70
 union all
 select 'Frank','English',80
 union all
 select 'Jerry','English',90
)
测试表数据:

/*每一行做一个判断,为Chinese就不可能是English所以补0*/
select name
   ,case subject when 'Chinese' then score else 0 end as Chinese
   ,case subject when 'English' then score else 0 end as English
 from ScoreTab
--未聚合之前行列转换后的数据:

 --演变聚合sum,实现行转列 这里用max进行聚合也是一样的,因为除了0也就一个(仅限于一个人对应科目
--数只有一次记录不会出现Tom 两次英语成绩这样的记录)
/*
select name
   ,sum(case subject when 'Chinese' then score else 0 end) as Chinese
   ,sum(case subject when 'English' then score else 0 end) as English
   from ScoreTab
   Group by name
*/
聚合之后真正实现行转列的数据:

 

 
------------------------===改进版动态拼接 SQL ===----------------------
 
05之前做法改进版--动态拼接SQL灵活实现行列转换
 
/*2005版本之前行列转换*/
Declare @sql nvarchar(4000)
set @sql='
;with ScoreTab(name,subject,score) as
(
 select ''Tom'',''Chinese'',80
 union all
 select ''Frank'',''Chinese'',90
 union all
 select ''Jerry'',''Chinese'',70
 union all
 select ''Tom'',''English'',70
 union all
 select ''Frank'',''English'',80
 union all
 select ''Jerry'',''English'',90
)
--用来准备cte,以后还是用临时表
select name'
;with ScoreTab(name,subject,score) as
(
 select 'Tom','Chinese',80
 union all
 select 'Frank','Chinese',90
 union all
 select 'Jerry','Chinese',70
 union all
 select 'Tom','English',70
 union all
 select 'Frank','English',80
 union all
 select 'Jerry','English',90
)
,DistinctSubjectTab as
(
 select distinct subject from ScoreTab
)
select @sql+='
   ,sum(case when subject in('''+subject+''') then score else 0 end) as '+subject
   from DistinctSubjectTab
set @sql+='
  from ScoreTab
  Group by name'
print @sql
execute (@sql)
效果图:

  

 
二、MSSQL2005及之后的版本支持 pivot(行转列函数),unpivot(列转行函数)
select * from ScoreTab pivot(sum(Score) for subject in(Chinese,English)) a
 
/*2005版本以后行列转换*/
Declare @sql nvarchar(4000)
set @sql='
;with ScoreTab(name,subject,score) as
(
 select ''Tom'',''Chinese'',80
 union all
 select ''Frank'',''Chinese'',90
 union all
 select ''Jerry'',''Chinese'',70
 union all
 select ''Tom'',''English'',70
 union all
 select ''Frank'',''English'',80
 union all
 select ''Jerry'',''English'',90
)
   select * from ScoreTab pivot(sum(score) for subject in ('
;with ScoreTab(name,subject,score) as
(
 select 'Tom','Chinese',80
 union all
 select 'Frank','Chinese',90
 union all
 select 'Jerry','Chinese',70
 union all
 select 'Tom','English',70
 union all
 select 'Frank','English',80
 union all
 select 'Jerry','English',90
)
,DistinctSubjectTab as
(
 select distinct subject from ScoreTab
)
,PivotDistinctSubjectTab(subject) as
(
  select  A.subject+','+B.subject FROM DistinctSubjectTab  A,DistinctSubjectTab B
  except
  SELECT  A.subject+','+B.subject FROM DistinctSubjectTab  A
            JOIN DistinctSubjectTab B
         ON A.subject=B.subject
)
select top 1 @sql+=subject+')) a
'  from PivotDistinctSubjectTab
print @sql
execute (@sql)

  

 
posted @ 2016-10-11 22:20  云雾散人  阅读(241)  评论(0编辑  收藏  举报

Your attitude not your aptitude will determine your altitude!

如果有来生,一个人去远行,看不同的风景,感受生命的活力!