mysql 列转行 unpivot_Unpivot 列转行

背景说明

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