适用范围

迁移PG到MogDB

crosstab

一个PG迁移至MogDB的项目中，发现源端语句中有crosstab语法，而MogDB目前不支持

分析后迁为常用的行转列语句

示例

数据环境

表test01来自test02。

CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2));

INSERT INTO EMP  VALUES(7369,'SMITH' ,'CLERK'     ,7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP  VALUES(7499,'ALLEN' ,'SALESMAN'  ,7698,to_date('20-02-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP  VALUES(7521,'WARD'  ,'SALESMAN'  ,7698,to_date('22-02-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP  VALUES(7566,'JONES' ,'MANAGER'   ,7839,to_date('02-04-1981','dd-mm-yyyy'),2975,NULL,20);