Hive行转列、列转行

野性酷女 2022-12-28 09:15 173阅读 0赞

**目录**

行转列

列转行

--------------------

# 行转列 #

**数据准备：**

create table stu_score(
        stu_id string,
        english bigint,
        math bigint,
        science bigint
    );
    
    insert into stu_score values
        ('甲',100,90,95),
        ('乙',95,90,100),
        ('丙',65,99,88);

**行转列：** 主要用到 **Leteral view explode**

select 
         stu_id
        ,subject,score
    from stu_score 
    LATERAL VIEW explode (
        map(
        'english',english,
        'math',math,
        'science',science
            ) ) tmp  
        as subject,score;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70][]

# 列转行 #

**数据准备：**

-- 直接拿行转列的结果演示
    create table stu_score_row  
    as
        select 
             stu_id
            ,subject,score
        from stu_score 
        LATERAL VIEW explode (
            map(
            'english',english,
            'math',math,
            'science',science
                ) ) tmp  
            as subject,score;

**列转行：**主要用到 **concat\_wc** 和 **collect\_list**

select 
        stu_id
        ,concat_ws(',',collect_list(subject)) as subs
    from stu_score_row
     group by stu_id
     ;
    
    -- collect_list 不去重，collect_set 去重。 字段类型需要是String

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 1][]

希望本文对你有帮助，请点个赞鼓励一下作者吧~ 谢谢！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70]: /images/20221120/cb977fdf9ef44c1d8779d8f8750a0387.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201229131615108.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx,size_16,color_FFFFFF,t_70