建表
drop table if exists analyst.fiona_colunm_to_row;
CREATE TABLE IF NOT EXISTS analyst.fiona_colunm_to_row AS
SELECT
'c' as row_1,'d' as row_2,1 as row_3
批量插入数据:
insert into analyst.fiona_colunm_to_row values
('c','d',2),
('c','d',3),
('e','f',1),
('e','f',2),
('e','f',3),
('e','f',4)
得到表格如下:
现在想要得到:
可以使用如下代码:
SELECT row_1,
row_2,
concat_ws(',' , collect_set(cast(row_3 AS string))) AS colunm_to_row
FROM analyst.fiona_row_to_column
GROUP BY row_1,
row_2;
其中:
collect_set作用有2个:
作用一:去重,去掉group by后面的重复元素
作用二:将group by后面属于同一组的第三列搞起来形成一个集合
concat_ws作用:
将collect_set形成的集合的每个元素之间添加逗号形成字符串
引用:https://blog.csdn.net/jsbylibo/article/details/82859168