背景说明
Oracle中UNPIVOT语法:
SELECT ...
FROM ...
UNPIVOT [INCLUDE|EXCLUDE NULLS]
(unpivot_clause
unpivot_for_clause
unpivot_in_clause )
WHERE ...
示例如下:
SQL> SELECT *
2 FROM pivoted_data
3 UNPIVOT (
4 deptsal --<-- unpivot_clause
5 FOR saldesc --<-- unpivot_for_clause
6 IN (d10_sal, d20_sal, d30_sal, d40_sal) --<-- unpivot_in_clause
7 );
JOB SALDESC DEPTSAL
---------- ---------- ----------
CLERK D10_SAL 1430
CLERK D20_SAL 2090
CLERK D30_SAL 1045
SALESMAN D30_SAL 6160
PRESIDENT D10_SAL 5500
MANAGER D10_SAL 2695
MANAGER D20_SAL 3272.5
MANAGER D30_SAL 3135
ANALYST D20_SAL 6600
解决方案
您可以在Polardb-O中使用Crosstab函数接口进行行列转换。
示例
转行示例如下:
with a as ( -- A对应原始数据(即需要列转行的数据)
select
js->>'seller' as seller,
js->>'se_year' as se_year,
jan ,
feb ,
mar ,
apr ,
may ,
jun ,
jul ,
aug ,
sep ,
oct ,
nov ,
dec
from crosstab(
-- 这个是需要进行行列变换的数据源。
-- 排序字段为group by字段,最后一个字段为转换后的内容字段,导数第二个字段为行列变换的字段(内容为枚举,比如月份)
-- (必须在下一个参数中提取出对应的所有枚举值)
$$select jsonb_build_object('seller', seller, 'se_year', se_year) as js, se_month, sum(se_amount) from tbl_sellers_info group by 1,2 order by 1$$,
-- 行列转换的行,有哪些值被提取出来作为列。 这个在这里代表的是月份,也就是se_month的值
-- 或(select * from (values('jan'),...('dec')) t(se_month))
'select distinct se_month from tbl_sellers_info order by 1'
)
as -- crosstab 输出格式
( js jsonb, -- 第一个参数SQL内对应的order by对应的字段(1个或多个)
Jan numeric, -- 第一个参数SQL内对应导数第二个字段的枚举值,(行转列)
feb numeric, -- ...同上
mar numeric,
apr numeric,
may numeric,
jun numeric,
jul numeric,
aug numeric,
sep numeric,
oct numeric,
nov numeric,
dec numeric
)
order by 1,2
)
,
-- b , 用jsonb把多列合并为一列,并使用jsonb_each展开。
b as (select seller, se_year, jsonb_each(row_to_json(a)::jsonb-'seller'::text-'se_year'::text) as rec from a)
select seller, se_year, (b.rec).key as month, (b.rec).value as sum from b;