一、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)