SQL | 行转列和列转行

如何使用SQL实现列转行和行转列？

• 1 背景
• 2 行转列
• • 2.1 读入数据
  • 2.2 需求
  • 2.3 SQL实现
  • • 2.3.1 第一种方法
    • 2.3.2 第二种方法
  • 2.4 总结
• 3 列转行
• • 3.1 需求1
  • 3.2 SQL实现
  • 3.3 需求2
  • 3.4 SQL实现

1 背景

为什么要做这个内容？一个是公司最近项目有涉及，另一个之前面试的时候也被问到过！所以总结一下~

2 行转列

2.1 读入数据

stu_id	c_name	grade
1	语文	80
1	1	数学	85
2	1	英语	90
3	2	语文	75
4	2	数学	95
5	2	英语	80
6	3	语文	95
7	3	数学	80
8	3	英语	79

2.2 需求

对于c_name这一列，将其转为列，即语文 数学 英语这三列！

2.3 SQL实现

2.3.1 第一种方法

select
	stu_id, 
	sum(case when c_name = '语文' then cast(grade as int) else 0 end) as chinese,
	sum(case when c_name = '数学' then cast(grade as int) else 0 end) as math,
	sum(case when c_name = '英语' then cast(grade as int) else 0 end) as english
from 
	tmp.tmp_sub_score
group by 
	stu_id
           

结果为：

stu_id	chinese	math	english
1	80	85	90
1	2	75	95	80
2	3	95	80	79

2.3.2 第二种方法

select
		stu_id as id,
		sum(if(c_name = "语文", cast(grade as int), 0)) AS chinese, -- 如果1个学生有多次考试 取平均就avg 取最大就max 取最小就min
		sum(if(c_name = "数学", cast(grade as int), 0)) AS math,
		sum(if(c_name = "英语", cast(grade as int), 0)) AS english
	from 
		tmp.tmp_sub_score
	group by 
		stu_id
           

结果为：

id	chinese	math	english
1	80	85	90
1	2	75	95	80
2	3	95	80	79

2.4 总结

• 行转列即 使用case when then else end 或者 if，即进行判断，然后赋值！外面包一层函数，根据具体需求确定
• 注意结合groupby使用 因为最后是一个id对应一个结果 所以是根据id进行分组 然后在组内进行操作！
• 如需要转换某一列的类型，使用cast函数即可！具体用法见上！

3 列转行

3.1 需求1

数据还是上面的数据，现在要做的事情是把stu_id进行汇总在一起

3.2 SQL实现

select 
	collect_set(stu_id) as stu_id_set,
	collect_list(stu_id) as stu_id_list
from
	tmp.tmp_sub_score
           

结果为：

stu_id_set	stu_id_list
["1","2","3"]	["1","1","1","2","2","2","3","3","3"]

结论：

• 无论是collect_set还是collect_list均可以实现列转行，但是一个是集合（不允许有重复元素），一个是列表（允许有重复元素）
• 具体使用哪个根据具体场景来做！

3.3 需求2

根据学生id将学科和成绩汇总成一条记录！

3.4 SQL实现

select 
	stu_id,
	collect_set(c_name) as c_name_set,
	collect_set(grade) as grade_set
from
	tmp.tmp_sub_score
group by 
	stu_id
           

结果为：

stu_id	c_name_set	grade_set
1	["语文","数学","英语"]	["80","85","90"]
1	2	["语文","数学","英语"]	["75","95","80"]
2	3	["语文","数学","英语"]	["95","80","79"]