SQL：行列转换

落日映苍穹つ 2022-05-15 00:55 263阅读 0赞

参考自：[https://www.cnblogs.com/janneystory/p/5622142.html][https_www.cnblogs.com_janneystory_p_5622142.html]

### 案例： ###

表scores

![509839-20160628075341062-366881156.jpg][]

请转成的横表是这样子的：

![509839-20160628075520015-564496996.jpg][]

答案;

select 姓名,
    SUM(case 课程 when  '语文' then 分数 else 0 end) as 语文,
    SUM(case 课程 when  '数学' then 分数 else 0 end) as 数学,
    SUM(case 课程 when  '物理' then 分数 else 0 end) as 物理
    from scores group by 姓名

### 补充知识: ###

链接:[mysql操作查询结果case when then else end用法举例][mysql_case when then else end]

### 思考过程: ###

既然这个表只有两列，那么可以根据姓名进行分组。先把姓名拼凑出来，后面的分数我们再想办法。

select 姓名 from scores group by 姓名

结果：

![509839-20160628075751109-1520936798.jpg][]

分析：

1.  我们先拿到语文这个科目的分数。既然我们用到了group by 语句，这里肯定要用聚合函数来求分数。
2.  而且我们只需要语文这一科的成绩，分组出来的 一共有 3列 ，分别是 语文、数学、物理  。  那么就需要判断科目来取分数。

这里符合我们需求的 case 语句就登场了。它和c\#中switch-case 作用一样。

sql case 语句语法：

case 字段
        when 值1 then 结果
        when 值2 then 结果2
        ...
        else 默认结果
    end

select 姓名,SUM(case 课程 when  '语文' then 分数 else 0 end) as 语文 from scores group by 姓名

结果:

![509839-20160628080312984-866796293.jpg][]

既然语文的分数取到了，其他科目改变下条件就可以了。

完整的sql:

### 横表转纵表 ###

我们先把刚刚转好的表，插入一个新表Scores2中。

select 姓名,
    SUM(case 课程 when  '语文' then 分数 else 0 end) as 语文,
    SUM(case 课程 when  '数学' then 分数 else 0 end) as 数学,
    SUM(case 课程 when  '物理' then 分数 else 0 end) as 物理
    into scores2
    from scores group by 姓名

我们也先把张三和李四的语文成绩查出来。

select 姓名,
     '语文' as 课程,
     语文 as 分数
     from scores2

结果：

![509839-20160628081119921-274692560.jpg][]

还有两科的数据怎么办呢？ 很简单，我们一个个都查出来，然后用 union all 把他们组合为一张表就可以了。

链接:[Union与Union All的区别][Union_Union All]

select 姓名,
     '语文' as 课程,
     语文 as 分数
     from scores2 
     union all
      select 姓名,
     '数学' as 课程,
     数学 as 分数
     from scores2 
     union all
      select 姓名,
     '物理' as 课程,
     物理 as 分数
     from scores2 
     order by 姓名 desc

结果：

![509839-20160628081423171-1597399170.jpg][]

但是大家有没有觉得很麻烦呢？别急，我们有更简单的办法。下面为大家介绍pivot关系运算符。

**pivot是sql server 2005 提供的运算符，所以只要数据库在05版本以上的都可以使用。主要用于行和列的转换。**

###  pivot纵表转横表 ###

select
        t2.姓名,
        t2.数学,
        t2.物理,
        t2.语文
    from Scores as t1
    pivot (sum(分数) for 课程 in(数学,语文,物理)) as t2

**pivot将原来表中 课程字段中的 数据行 数学,语文,物理 转换为列，并用sum取对应列的值。**

我们只需要记住它的用法就可以了。

### unpivot 横表转纵表 ###

select
         *
     from
     scores2
     unpivot (分数 for 课程 in (语文,数学,物理)) as t3

** unpivot 将 语文,数学,物理 列转为行，分数为新的一列存放对应的值。**

### 另外 ###

我们还可以使用decode函数

链接:[oracle/MySQL 中的decode的使用][oracle_MySQL _decode]

答案亦可:

if:
    
    select student,
    SUM(if(subject='语文',score,0 )) as 语文,
    SUM(if(subject='数学',score,0 )) as 数学,
    SUM(if(subject='英语',score,0 )) as 英语
    from scores group by student ;
    
    pivot:
    
    (含id要去掉id)
    select * 
    from (select "year","month","amount" from "test4") 
    pivot (
    sum("amount")  
    for "month" 
    in (1 m1,2 as m2,3 as m3) );
    
    (不含id)
    select * 
    from "student"
    pivot (
    sum("score")  
    for "subject" 
    in ("语文" as 语文,"数学"as 数学,"英语" as 英语) );

[https_www.cnblogs.com_janneystory_p_5622142.html]: https://www.cnblogs.com/janneystory/p/5622142.html
[509839-20160628075341062-366881156.jpg]: /images/20220515/6eab7f2b8bba4354b091e487428ce351.png
[509839-20160628075520015-564496996.jpg]: /images/20220515/eb79214ed7144b7d974d2fffa490efcf.png
[mysql_case when then else end]: https://www.cnblogs.com/clphp/p/6256207.html
[509839-20160628075751109-1520936798.jpg]: /images/20220515/c0c73aaafa49421e8c6f960e92c98604.png
[509839-20160628080312984-866796293.jpg]: /images/20220515/06a1b598eace4d77ad0d9d59aa45fa43.png
[509839-20160628081119921-274692560.jpg]: /images/20220515/4078cdd7d0ea4992b6333d1ba3afedab.png
[Union_Union All]: https://www.cnblogs.com/wcl2017/p/7078685.html
[509839-20160628081423171-1597399170.jpg]: /images/20220515/82a5d0841b0b405fb6589c46002adea3.png
[oracle_MySQL _decode]: https://www.cnblogs.com/zt007/p/6170417.html