SQL面试——列转行，行转列

数据长这样

张三,大唐双龙传
李四,天下无贼
张三,神探狄仁杰
李四,霸王别姬
李四,霸王别姬
王五,机器人总动员
王五,放牛班的春天
王五,盗梦空间

多行转一行

使用cancat_ws（等同wm_concat ）、collect_list、collect_set、sort_array
这些函数分别是哪个数据库的，分别有什么区别稍后补上。

-- group_concat
-- 使用group_concat，mysql
SELECT locus, GROUP_CONCAT(id) FROM info 
WHERE locus IN('AB086827','AF040764') GROUP BY locus

-- wm_concat
-- 相对应的是Oracle独有的wm_concat，一般搭配group by 使用。阿里中台亦可用

-- concat_ws
-- 使用给定的分隔符将多个输入字符串列连接到一个字符串列中
-- 这个方法在阿里中台亲测可用
-- 只不过注意ent_name, id1 两个都必须出现在group by之后
select ent_name, id1, concat_ws(',',collect_list(cons_no)) as cons_no 
from power_data_day
GROUP BY ent_name, id1;

-- concat_ws的另一个用法是将hive的list或者array数据拼成string类型。hive中直接用cast转换list类型数据会报错。concat_ws(";",array_data) as 

-- collect_list
---使用逗号分隔，但是有中括号[]
select username, collect_list(video_name) 
from t_visit_video 
group by username ;

-- collect_set功能：聚合函数，返回去除重复元素后的集合
select username, id, collect_set(video_name) 
from t_visit_video 
where username is not null
group by username , id ;

-- sort_array功能：集合函数，对输入数组元素按升序或降序排序。空元素将按升序放在返回数组的开头，或按降序放在返回数组的末尾。

collect_set函数的搭配使用见下
https://blog.csdn.net/weixin_43597208/article/details/117450304

多行转一列

数据如图：
pref_name, sex, population
AA, 1, 100
AA, 2, 104

变成下面：
pref_name, M, F
AA， 100， 104
详细数据减附图1

select
	pref_name,
	sum( case when sex='1' then population else 0 end ) as cnt_M,
	sum( case when sex='0' then population else 0 end ) as cnt_F
from table1
group by pref_name

多列转一列

table_a和table_b，在下面的代码中其实是同一张表的不同筛选条件而已，甚至可以是同一张表。如此即可实现同一张表的不同的列，转成一列

select col1, col3 from table_a
union all
select col1, col2 from table_b

一行拆成多行

-- trans_array
-- 阿里中台


-- explode
-- 对数据格式有要求explode() takes an array or a map as a parameter
select explode(subs) as sub from employees ;


-- 如果是逗号分割的字符串等，使用下面代码

select app from specter.userapp_list 
lateral view explode(split(applist,',')) r1 AS app;

一行拆成多列

M列转换为N行

stack函数

附录

附图1