如何在Hive中实现动态行转列，将Status值转为对应数量列？

需求说明 #

实现将Customer、Status、Quantity三字段的长表转为宽表，每个客户占一行，所有Status取值自动转为独立列，无匹配值填充0，无需手动枚举固定Status取值，效果和SAS的proc transpose完全一致。

方案1：固定Status取值实现（仅适合枚举值已知且不变的场景） #

如果提前明确所有Status的取值，可以直接用case when加聚合实现：

select 
    Customer,
    sum(case when Status = 'Paid' then Quantity else 0 end) as Paid,
    sum(case when Status = 'N Paid' then Quantity else 0 end) as `N Paid`,
    sum(case when Status = 'Open' then Quantity else 0 end) as Open
from 你的原始表名
group by Customer;

注：如果同一个客户同一种Status仅存在1条记录，可将sum替换为max，效果一致。

方案2：动态行转列实现（自动识别所有Status取值，适配Status不固定的场景） #

无需手动修改SQL枚举Status，分为两步实现：

• 第一步：查询所有不重复的Status，拼接成动态SQL片段

select concat_ws(',', 
    collect_set(
        concat("sum(case when Status = '", Status, "' then Quantity else 0 end) as `", Status, "`")
    )
) as sql_segment
from 你的原始表名;

执行后会得到类似sum(case when Status = 'Paid' then Quantity else 0 end) as Paid,sum(case when Status = 'N Paid' then Quantity else 0 end) as N Paid,sum(case when Status = 'Open' then Quantity else 0 end) as Open``的结果，即后续需要用到的列逻辑片段。

• 第二步：将第一步得到的SQL片段替换到如下语句中，执行即可得到最终结果

select 
    Customer,
    -- 此处替换为第一步查询得到的sql_segment内容
from 你的原始表名
group by Customer;

如果需要完全自动化执行，不需要人工复制片段，可以用Shell、Python等脚本封装整个流程：

1. 执行第一步SQL，将返回的sql_segment存入变量
2. 拼接得到完整的行转列SQL
3. 调用hive命令执行拼接后的SQL即可

高版本Hive简化实现（Hive 2.3及以上版本） #

Hive 2.3开始原生支持pivot函数，写法更简洁：

-- 固定Status写法
select * from 你的原始表名
pivot (
    sum(Quantity)
    for Status in ('Paid' as Paid, 'N Paid' as `N Paid`, 'Open' as Open)
) as t;

动态使用时同样先查询所有Status取值，拼接成'取值1' as 别名1, '取值2' as 别名2的片段替换到in子句中即可，逻辑和上述动态方案一致。

内容的提问来源于stack exchange，提问作者Marcio Lino