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

注：本例中未来被转为列的字段值要相对固化，比如季度只有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

执行结果