使用PIVOT，UNPIVOT进行行列转换。

填充空白 2023-04-21

557

碎碎叨叨：面试遭遇了个熟悉而陌生的问题。SQL如何行列转换问题。刷新下盲区。当行比较多时，如何像excel一样用透视表快速形成二维表？当列比较多时，如何快速进行列行转换？

已知以下2表

表名：score，字段名分别为name,subject,score

表名：NewScores，字段名分别为，name,语文，英语，生物，数学（即为表1透视后的二维展示形式）

本文包括以下内容：

一、行转列：如何从表score，转换为表NewScores？

二、列转行：如何从表NewScores，转换为表score？

图为：西湖的水

一、如何从表score，转换为表NewScores？

方法1：使用CASE WHEN，静态SQL转换

第一反应肯定是用case when,分别查询出对应科目的值，作为列的值。当然也可以分别查询出对应的值，左右关联起来（这个方法就显得略笨）。这里用CASE WHEN做判断后转换。

select name ,
  max(case  when subject='语文' then score else 0 end) as '语文',
  max(case  when subject='数学' then score else 0 end)  as '数学',
  max(case  when subject='英语' then score else 0 end) as '英语',
  max(case  when subject='生物' then score else 0 end) as '生物'
from score
group by name

方法2：使用 PIVOT.注意在SQLserver下才有该函数。

在此例中，当subject的值不是很多时，用方法1是可以的，但是subject值很多时，用方法1就显得很麻烦了。

此时可用PIVOT函数，PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。但是需注意PIVOT在SQLSERVER中支持，MYSQL数据库不支持。

以下是 PIVOT 的语法

###PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。
####以下是带批注的 PIVOT 语法：####
SELECT  非透视的列，第一个透视的列 as 列名，第2个透视的列 as 列名，。。。最后一个透视的列 as 列名
/*转换后要取的数据列，包括非透视的列以及透视的列*/
FROM  源查询的表名
PIVOT
(     聚合函数（value_column）/*行转列后，列的值*/
FOR  pivot_column       /*需要行转列，形成的列**/
IN (<column_list>)      /*透视的列的值*/
) AS 透视表的别名
<可选的 ORDER BY 子句>

套入以上语法，从表score转换为表NewScores的SQL代码为：

SELECT
    NewScores.*
FROM
    score
PIVOT (
max(score /*行转列后 列的值*/)FOR Subject /*需要行转列的列*/
IN ([语文],[英语],[生物],[数学]/*列的值*/)
) AS NewScores

输出结果如下：

二、如何从表NewScores，转换为表score

方法1：使用Union all

第一反应肯定是分别取出不同subject的score,用union all,做上下关联。

select n.name, '语文' as subject, n.语文 as score from NewScores n
union all
select n.name, '英语' as subject, n.英语 score from NewScores n
union all
select n.name, '生物' as subject, n.生物 score from NewScores n
union all
select n.name, '数学' as subject, n.数学 score from NewScores n

方法2：使用UNPIVOT

以上中，PIVOT类似excel中数据透视表功能。那如何将数据透视表结果还原为一维表呢。对应PIVOT的还原，也有个函数就是UNPIVOT。

需注意UNPIVOT也是在SQLSERVER中支持，MYSQL数据库不支持。

以下是 UNPIVOT 的语法

##UNPIVOT用于将列名转为列值（即列转行），
SELECT  转换后的数据字段(未转换的字段,pivot_column,value_column)
table_source /*要转换的表*/
UNPIVOT(
value_column      /*值转换为的列名*/
FOR pivot_column  /* for表示循环，pivot_column为所要形成的行的字段名*/
IN(<column_list>) /*列转行，所要形成的行所取的列字段*/
)

用 UNPIVOT函数进行列转行，使用到以上例子中，代码如下：

SELECT
s.name, s.subject,s.score
FROM
NewScores
    UNPIVOT (
    score FOR /*循环*/
    subject IN (语文,英语,生物,数学)
    )s

输出结果如下：

欢迎一起探讨好玩的分析和查询命题，或者数据驱动增长的话题，有兴趣可私聊加微~加油~好运~

文章转载自填充空白，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

使用PIVOT，UNPIVOT进行行列转换。

评论