SQL server 动态行转列

在学习数据库的时候，遇到了怎么把数据表中的内容转变成数据表的字段，在此，记录一下自己对行转列的理解

比如有个学生成绩表：

stuid:学号

course:科目

score：成绩

表的内容为：

stuid　　course　　score

0101　　语文　    　78

0101　　数学    　　90

0101　　英语　    　67

0101　　物理    　　88

而我们想要的是类似下表：

stuid　　语文　　数学　　英语　　物理

0101 　　78　　90　　　67　　  88

这个时候就需要用到行转列，行转列有动态与静态之分：

静态行转列：通过sql语句，静态的进行转换，一旦原表的数据有改动，比如增加化学成绩，或者删除物理成绩，我们就得重新改变sql语句：

就上表，转换的sql语句为：

select stuid,

max(case course when '语文' then score else 0 end)语文,
max(case course when '数学' then score else 0 end)数学,
max(case course when '英语' then score else 0 end)英语,
max(case course when '物理' then score else 0 end)物理
from scores    --表名
 group by stuid  --分组查询

主要知识：max（） ，case，group by 分组查询。

max（）取最大值。

case：我的理解是从几个选项中选择，比如：

case course when '语文' then score else 0 end
当course 为语文时，case返回对应的score与0当中的一个，在本例中，查询第一条数据：

0101　　语文　    　78
此时：course=‘语文’，score=78，则case返回78，
当查询第二条数据的时候：
0101　　数学    　　90
course=‘语文’不存在，则返回 0 （else 0 ）
以此类推得：

max(78,0,0,0),
max（）取最大值，最后的数据就是 78，

所谓静态，就是我们手动静态的获取每一个字段（语文，数学，英语，物理），一旦科目有所改变，我们就得修改sql语句，不怎么方便

而动态行转列就可以避免这种情况，它是动态的自己根据原表中的数据，获取字段名：

declare @sql varchar(8000) --申明一个变量 @sql，数据类型为 varchar（8000）
set @sql='select stuid,'      -- 使用 set 为@sql 赋值
select @sql =@sql +'max(case course when '''+course +'''then score else 0 end)'+''''+course +''','
from (select distinct course from scores) as sc           --使用select 为@sql赋值
set @sql =left(@sql,len(@sql)-1)+'from scores group by stuid'
exec(@sql)    --执行@sql

注意：在sql语句中，使用单引号 ’  来确定字符串的范围，如果字符串本身含有单引号如：‘  姓名：‘张三’，性别：‘男’  ’，这时候需要用 '' ,即两个单引号来表示字符串本身的单引号。

set 语句大家应该很熟悉，为变量赋值，而select 其实也可以看做一个赋值关键字，不过是一个循环赋值（个人理解）而已。

如 ：select stuid from students,表示将students表中的所有stuid属性值（1,2,3,4......）赋值给变量stuid：

stuid=1,对stuid操作（如输出stuid=1）

stuid=2,对stuid操作（如输出stuid=2）

stuid=3,对stuid操作（如输出stuid=3）

stuid=4........

所以，上边的动态行转列中的select赋值语句可以理解为：

将from 后边的（select course from scores）所查询到的结果，逐一赋值给course变量，并且，每一次复制后的操作为：字符串连接

所以

select @sql =@sql +'max(case course when '''+course +'''then score else 0 end)'+''''course +''','
from (select distinct course from scores)的执行过程为：
@sql='select stuid,'+'max(case course when '''+语文+'''then score else 0 end)'+''''语文+''',' --@1
@sql=@1+'max(case course when '''+数学+'''then score else 0 end)'+''''数学+''','   --@2

@sql=@2+'max(case course when '''+英语+'''then score else 0 end)'+''''英语+''','   --@3

@sql=@3+'max(case course when '''+物理+'''then score else 0 end)'+''''物理+''','   --@4
是不是和静态的代码很像？因为它们的原理都是一样的:max()，case，group by,不同的是动态行转列使用动态拼接字符串的方法，动态的从原表当中找出我们需要的字段，如果原表当中删除了物理成绩，我们就查不到物理成绩，自然也就不会将科目‘物理’加入到结果当中，如果原表增加了化学成绩，我们同样可以查到 化学成绩，并将其加入到结果当中，最后，通过exec语句执行@sql，这就是动态的行转列了。