数据库之SQL行列转换

SQL行列转换

数据分析经常会遇到行列转换，SQL中的行列转化集可以用常规的case when/union all语句，也可以用专门的行列转换函数pivot和unpivot。

数据表表1（T1）如下表所示，是数据库常见的存储形式，课程的分数按行记录：

数据表表2（T2）如下表所示，课程的分数按列记录：

case when/union all

• case when 将行记录转化为列记录（T1->T2）

select 姓名，
sum(case when 课程='语文' then 成绩 else 0) as 语文，
sum(case when 课程='数学' then 成绩 else 0) as 数学，
sum(case when 课程='英语' then 成绩 else 0) as 英语
from T1
group by 姓名

• union all 列记录转化成行记录

select 姓名,
'语文' as 课程,#'语文'字符串为课程列的值
语文 as 分数 #语文列的数值作为分数
from T2
union all
select 姓名,
'数学' as 数学,
语文 as 分数
from T2
union all
select 姓名,
'英语' as 英语,
英语 as 分数
from T2
order by 姓名 desc

• UNION和 UNION ALL的区别是，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
• 另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

pivot/unpivot

• pivot 将行记录转化为列记录（T1->T2）

select T2.姓名,T2.语文，T2.数学，T2.英语
from T1
pivot(
sum(分数) #目标表（T2）的列值来源于源表（T1）的哪个列
for 课程 #目标表（T2）的列标签来源于源表（T1）的哪个列
in(语文,数学,英语)#目标表(T2)的列名
) as T2

• unpivot 将列记录转化为行记录（T2->T1）

select T1.姓名,T1.课程，T1.分数
from T2
unpivot(
分数 #源表（T2）的列值的在转化后的表（T1）中的列标签
for 课程 #源表（T2）的所有列标签在转化后的表（T1）中的列标签
in(语文,数学,英语)#源表（T2）的列标签都有哪些
)as T1