背景说明

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 --

5 FOR saldesc --

6 IN (d10_sal, d20_sal, d30_sal, d40_sal) --

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;