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"]