【HQL】HiveQL 列转行

  上周写了个 HiveQL 的行转列，这种来一个列转行吧。没看过的可以先看之前的文章。

【HQL】HiveQL 行转列

今天还是用上次的结果表，将他列转行转成原来的表，不过这次多加几行。

# 先定义一张表
WITH order_table AS (
    SELECT * FROM(
    SELECT
        '123' as user_id,
        '202205010001' as order_id
    UNION
    SELECT
        '123' as user_id,
        '202205010002' as order_id
    UNION
    SELECT
        '123' as user_id,
        '202205010003' as order_id
    UNION
    SELECT
        '234' as user_id,
        '202205140001' as order_id
    UNION
    SELECT
        '234' as user_id,
        '202205140002' as order_id
    UNION
    SELECT
        '234' as user_id,
        '202205140003' as order_id)
    ORDER BY user_id, order_id
)
SELECT * FROM order_table;

表结构：

user_id	order_id
123	202205010001
123	202205010002
123	202205010003
234	202205140001
234	202205140002
234	202205140003

要求：

将统一用户的 order_id 放在同一列，即列转行。

SELECT   user_id,
         concat_ws(',',collect_set(order_id)) AS order_list
FROM     order_table
GROUP BY user_id
ORDER BY user_id;

结果：

user_id	order_list
123	202205010001,202205010002,202205010003
234	202205140001,202205140002,202205140003

欢迎关注。