行转列，列转行

mysql的行转列，列转行(但是对多列进行操作)

列转行

运行前：

运行后：

-- 创建学生表
CREATE  TABLE test.stu_score
(
   username VARCHAR(20),        -- 学生姓名
   subject VARCHAR(30),        -- 科目
   score FLOAT               -- 成绩
);

-- 插入数据
use test;
INSERT INTO stu_score SELECT '张三', '语文', 80;
INSERT INTO stu_score SELECT '张三', '数学', 90;
INSERT INTO stu_score SELECT '张三', '英语', 70;
INSERT INTO stu_score SELECT '张三', '生物', 85;
INSERT INTO stu_score SELECT '李四', '语文', 80;
INSERT INTO stu_score SELECT '李四', '数学', 92;
INSERT INTO stu_score SELECT '李四', '英语', 76;
INSERT INTO stu_score SELECT '李四', '生物', 88;
INSERT INTO stu_score SELECT '码农', '语文', 60;
INSERT INTO stu_score SELECT '码农', '数学', 82;
INSERT INTO stu_score SELECT '码农', '英语', 96;
INSERT INTO stu_score SELECT '码农', '生物', 78;

SELECT  * from stu_score ;
-- 列转行
-- 这里的max是为了将无数据的值设为0，避免出现null
SELECT username ,
    MAX(CASE subject WHEN '数学' THEN score ELSE 0 END ) 数学,
    MAX(CASE subject WHEN '语文' THEN score ELSE 0 END ) 语文,
    MAX(CASE subject WHEN '英语' THEN score ELSE 0 END ) 英语,
    MAX(CASE subject WHEN '生物' THEN score ELSE 0 END ) 生物
FROM stu_score
GROUP BY username;

行转列

运行前：

运行后：

源sql：

CREATE  TABLE test.stu_score2
(
   username VARCHAR(20),        -- 学生姓名
   math int,        -- 科目
   chinese int,
   english int,
   biological int
);
DROP  TABLE  test.stu_score2;
-- 插入数据
use test;
INSERT INTO stu_score2 SELECT '张三', 90, 80,70,85;
INSERT INTO stu_score2 SELECT '李四', 92, 80,76,88;
INSERT INTO stu_score2 SELECT '王五', 82, 60,96,78;

SELECT * FROM test.stu_score2;

-- 行转列
select username, '数学' COURSE , math as score from test.stu_score2
UNION
select username, '语文' COURSE, chinese as SCORE from test.stu_score2
UNION
select username, '英语' COURSE, english as SCORE from test.stu_score2
UNION
select username, '生物' COURSE, biological as SCORE from test.stu_score2
order by username,COURSE;

hive的行转列，列转行

列转行

concat_ws( ‘,’ , collect_list(column))

hive> select * from col_lie limit 10;
OK
col_lie.user_id    col_lie.order_id
104399    1715131
104399    2105395
104399    1758844
104399    981085
104399    2444143
104399    1458638
104399    968412
104400    1609001
104400    2986088
104400    1795054

根据id进行分组，并用逗号进行区分

select user_id,
concat_ws(',',collect_list(order_id)) as order_value
from col_lie
group by user_id
limit 10;

//结果(简写)
user_id    order_value
104399    1715131,2105395,1758844,981085,2444143

备注：collect_list不去重，collect_set去重。column的数据类型是String

行转列

lateral view explode( split ( order_value , ‘ , ‘ )

hive> select * from lie_col;
OK
lie_col.user_id    lie_col.order_value
104408    2909888,2662805,2922438,674972,2877863,190237
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128
104406    1463273,2351480,1958037,2606570,3226561,3239512,990271,1436056,2262338,2858678
104405    153023,2076625,1734614,2796812,1633995,2298856,2833641,3286778,2402946,2944051,181577,464232
104404    1815641,108556,3110738,2536910,1977293,424564
104403    253936,2917434,2345879,235401,2268252,2149562,2910478,375109,932923,1989353
104402    3373196,1908678,291757,1603657,1807247,573497,1050134,3402420
104401    814760,213922,2008045,3305934,2130994,1602245,419609,2502539,3040058,2828163,3063469
104400    1609001,2986088,1795054,429550,1812893
104399    1715131,2105395,1758844,981085,2444143,1458638,968412

按照value进行切割成单元素

select user_id,order_value,order_id
from lie_col
lateral view explode(split(order_value,',')) num as order_id
limit 10;

//结果
user_id    order_value    order_id
104408    2909888,2662805,2922438,674972,2877863,190237    2909888
104408    2909888,2662805,2922438,674972,2877863,190237    2662805
104408    2909888,2662805,2922438,674972,2877863,190237    2922438
104408    2909888,2662805,2922438,674972,2877863,190237    674972
104408    2909888,2662805,2922438,674972,2877863,190237    2877863
104408    2909888,2662805,2922438,674972,2877863,190237    190237
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128    2982655
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128    814964
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128    1484250
104407    2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128    2323912