(mssql)动态实现由纵变横实例整理

很多情况下，需要对数据表中的数据由纵向变横向显示

也就是由较少的列根据一定的条件转化成较多的列显示。

下面的学生成绩表就是其中的一个例子：
/*
if object_id(’tempdb..#s’) is not null
drop table #s
create table #s(stu char(8)，subject char(8)，score int)

insert #s(stu，score，subject)
select ’小李’ ， 88 ， ’数学’
union all select ’小李’ ， 77 ， ’英语’
union all select ’小李’ ， 55 ， ’政治’
union all select ’小李’ ， 30 ， ’语文’
union all select ’小赵’ ， 66 ， ’数学’
union all select ’小赵’ ， 23 ， ’英语’
union all select ’小赵’ ， 77 ， ’政治’
union all select ’小赵’ ， 87 ， ’语文’
union all select ’小a’ ， 22 ， ’数学’
union all select ’小a’ ， 74 ， ’英语’
union all select ’小a’ ， 99 ， ’政治’
union all select ’小a’ ， 23 ， ’语文’
union all select ’小b’ ， 77 ， ’数学’
union all select ’小b’ ， 78 ， ’英语’
union all select ’小b’ ， 83 ， ’政治’
union all select ’小b’ ， 89 ， ’语文’
union all select ’小c’ ， 82 ， ’数学’
union all select ’小c’ ， 89 ， ’英语’
union all select ’小c’ ， 78 ， ’政治’
union all select ’小c’ ， 90 ， ’语文’
union all select ’小d’ ， 89 ， ’数学’
union all select ’小d’ ， 89 ， ’英语’
union all select ’小d’ ， 56 ， ’政治’
union all select ’小d’ ， 23 ， ’语文’
union all select ’小1’ ， 99 ， ’数学’
union all select ’小1’ ， 23 ， ’英语’
union all select ’小1’ ， 87 ， ’政治’
union all select ’小1’ ， 53 ， ’语文’
union all select ’小g’ ， 88 ， ’数学’
union all select ’小g’ ， 23 ， ’英语’
union all select ’小g’ ， 87 ， ’政治’
union all select ’小g’ ， 22 ， ’语文’
union all select ’小2’ ， 88 ， ’数学’
union all select ’小2’ ， 78 ， ’英语’
union all select ’小2’ ， 67 ， ’政治’
union all select ’小2’ ， 30 ， ’语文’
union all select ’小3’ ， 77 ， ’数学’
union all select ’小3’ ， 68 ， ’英语’
union all select ’小3’ ， 37 ， ’政治’
union all select ’小3’ ， 87 ， ’语文’
union all select ’小4’ ， 54 ， ’数学’
union all select ’小4’ ， 67 ， ’英语’
union all select ’小4’ ， 23 ， ’政治’
union all select ’小4’ ， 77 ， ’语文’
union all select ’小5’ ， 87 ， ’数学’
union all select ’小5’ ， 45 ， ’英语’
union all select ’小5’ ， 43 ， ’政治’
union all select ’小5’ ， 67 ， ’语文’
union all select ’小6’ ， 34 ， ’数学’
union all select ’小6’ ， 87 ， ’英语’
union all select ’小6’ ， 34 ， ’政治’
union all select ’小6’ ， 45 ， ’语文’
union all select ’小7’ ， 78 ， ’数学’
union all select ’小7’ ， 89 ， ’英语’
union all select ’小7’ ， 34 ， ’政治’
union all select ’小7’ ， 89 ， ’语文’
union all select ’小8’ ， 45 ， ’数学’
union all select ’小8’ ， 89 ， ’英语’
union all select ’小8’ ， 56 ， ’政治’
union all select ’小8’ ， 89 ， ’语文’
union all select ’小9’ ， 23 ， ’数学’
union all select ’小9’ ， 67 ， ’英语’
union all select ’小9’ ， 34 ， ’政治’
union all select ’小9’ ， 78 ， ’语文’
*/

select * from #s

这样的结构非常合理。可往往在查看数据时需要这样的排列方式:

学生姓名   数学  英文   政治  语文 ... 总成绩
.
.
.

看到了吧？这就是一个由3列数据显示要转化成更多列的显示。

[静态方法]

要实现上述的排列方式，对MSSQL而言非常简单，采用聚合函数即可完成：

select stu，
    sum(case subject when ’数学’ then score else 0 end) 数学成绩，
    sum(case subject when ’英语’ then score else 0 end) 英文成绩，
    sum(case subject when ’政治’ then score else 0 end) 政治成绩，
    sum(case subject when ’语文’ then score else 0 end) 语文成绩，
    
    sum(stuscore) 总成绩
from #t group by stu

--实现总成绩由高到低排列
order by sum(score) desc


这样，就较简单的实现了"由纵变横"的转化

然而，在MIS中的查询中会遇到各种查询，在开发中无法预知将要查询哪几科的成绩

所以上面的句子的适用范围很小，还需要对它进行扩展。

[动态构造]

观察红色部分，可以发现除了"科目"名称之外，剩下的都是共有的部分，所以可以想到只要能构造出一个

能产生红色部分的语句，再添加上头尾部分，就完成了动态构造的功能！

--定义动态SQL变量
declare @sql nvarchar(1000)

--先添加头部！
set @sql=’select stu 学生姓名’

--动态构造，仿造红色部分

select @sql = @sql+’，sum(case subject when ’’’+subject+’’’ then score else 0 end) ’+subject+’’
--只要能完成distinct子查询，就能查询出任意课程的成绩！！
from (select distinct subject from #s where subject in(’数学’，’语文’)) a

--最后添加尾部
set @sql= @sql+’，sum(score) 总分 from #s group by stu order by sum(score) desc’

--最后执行
exec(@sql)

[说明]
动态部分的distinct部分很关键。IN部分往往要做成参数，如果要封装成存储过程的话，这部分就得借用
临时表来实现，因为IN后面不允许是变量。