动态行转列SQL（非case when、非sum if）

原创已于 2023-03-27 10:19:37 修改 · 304 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hive #sql

于 2023-03-21 17:24:54 首次发布

该文展示了如何使用SQL将带有年份和季度的数据转换为按年份分别显示每个季度金额的列格式，利用CONCAT函数和STR_TO_MAP函数进行数据结构的重塑。

注：本例中未来被转为列的字段值要相对固化，比如季度只有4个，月份只有12个等等

with t as (
    select 
      year,quarter,amount
    from values 
       (1991,1,600)
      ,(1991,2,500)
      ,(1991,3,400)
      ,(1991,4,290)
      ,(1992,1,700)
      ,(1992,2,800)
      ,(1992,3,900)
      ,(1992,4,650)
    t (year,quarter,amount)
)
,t1 as (
    select
         year
        ,concat(quarter,":",amount) as amount
    from t
)
,t2 as (
    -- 生成map数据
    select 
         year
        ,str_to_map(CONCAT_WS('&',COLLECT_SET(amount)),"&",":" ) amount       -- 间夜量/map类型
    from t1 
    group by year
    order by year
)
select
     year
    ,amount[1] as q1    -- month1 amount
    ,amount[2] as q2    -- month2 amount
    ,amount[3] as q3    -- month3 amount
    ,amount[4] as q4    -- month4 amount
from t2
order by year

执行结果