Hive - [06] 行转列，列转行

 

行转列（多行转一行）

1、创建表，并插入示例数据。

create table  students_info(
    `SNO` string comment '学生编号',
    `name` string comment '姓名',
    `DEPART` string  comment '选修课程'
)
-- 学生信息表数据插入
insert into  students_info  values 
(103,'张三','心理学'),
(103,'张三','Java'),
(105,'王五','Spark'),
(109,'李麻子','Flink'),
(109,'李麻子','Kylin');

select * from  students_info;

2、行转列

select 
    max(sno), 
    name,
    concat_ws(',', collect_set(DEPART)) as DEPART 
from students_info
group by name;

 

 

列转行（一行转多行）

1、创建表，并插入示例数据。

create table  students_info(
    `SNO` string comment '学生编号',
    `name` string comment '姓名',
    `DEPART` string comment '选修课程'
)

-- 成绩表数据插入
insert into  students_info  values 
(103,'张三','心理学,Java'),
(105,'王五','Spark'),
(109,'李麻子','Flink,Kylin');

select * from  students_info;

2、列转行

select SNO, name, add_DEPART
from students_info si 
lateral  view explode(split(si.DEPART,',')) b AS add_DEPART;

 

 

 

案例一

-- 源数据
101234 1 检查
101234 2 维修
101235 1 检查
101235 2 检查
101236 3 检查

-- 目标展示
101234 1-2 检查-维修
101235 1-2 检查
101236 3 检查

源数据的查询SQL

with tt_repair as (
    select '101234' as repair_no, 1 as repair_type, '检查' as repair_plan
    union all
    select '101234' as repair_no, 2 as repair_type, '维修' as repair_plan
    union all
    select '101235' as repair_no, 1 as repair_type, '检查' as repair_plan
    union all
    select '101235' as repair_no, 2 as repair_type, '检查' as repair_plan
    union all
    select '101236' as repair_no, 3 as repair_type, '检查' as repair_plan
)
select repair_no
    ,concat_ws('-',collect_list(repair_type))
    ,concat_ws('-',collect_list(repair_plan))
from tt_repair
group by repair_no;

达成目标展示效果的SQL（case1）

select
s_id,
group_concat(age separator '-') age,
group_concat(distinct addr separator '-') addr
from
test_tidb.student_1 group by s_id;

 达成目标展示效果的SQL（case2）

select
    s_id,
    concat_ws('-',collect_list(age)) as age,
    concat_ws('-',collect_set(addr)) as addr
from
    test.student_2
group by
    s_id;

 

 

 

— 要养成终生学习的习惯 —