DB2行转列（多维度）

多维度下进行行列转换，下面的行列转换时根据客户，所属银行机构进行的行列转换。

-----------------建表

CREATE TABLE CUST_BANK_INFO

(

 CUST_ID VARCHAR(10),

 CUST_NAME VARCHAR(100),

 BANK_NO VARCHAR(100),

 BANK VARCHAR(100),

 MONEY_TYPE_NO VARCHAR(100),

 MONEY_TYPE VARCHAR(100),

 MONEY INT

);

INSERT INTO CUST_BANK_INFO VALUES('C001','允贤','B01','中国银行','MT01','理财',10000);

INSERT INTO CUST_BANK_INFO VALUES('C002','李四','B01','中国银行','MT02','贷款',20390);

INSERT INTO CUST_BANK_INFO VALUES('C003','王五','B01','中国银行','MT03','存款',29301);

INSERT INTO CUST_BANK_INFO VALUES('C004','陈六','B01','中国银行','MT04','教育储蓄',10000);

INSERT INTO CUST_BANK_INFO VALUES('C001','允贤','B02','中国银行','MT01','理财',20000);

INSERT INTO CUST_BANK_INFO VALUES('C005','JACK','B02','工商银行','MT05','理财',10001);

INSERT INTO CUST_BANK_INFO VALUES('C006','王三','B02','工商银行','MT06','贷款',10002);

INSERT INTO CUST_BANK_INFO VALUES('C007','刘六','B02','工商银行','MT07','存款',10003);

INSERT INTO CUST_BANK_INFO VALUES('C008','郑七','B02','工商银行','MT08','教育储蓄',10004);

SELECT * FROM CUST_BANK_INFO

查询结果如下：

--DELETE FROM CUST_BANK_INFO

DROP TABLE Mapping;

CREATE TABLE Mapping

(

 CN_NAME VARCHAR(100),

 Code_L  VARCHAR(10),

 Code_N  VARCHAR(10)

)

INSERT INTO Mapping values('理财','MT01','BANK_01');

INSERT INTO Mapping values('理财','MT05','BANK_01');

INSERT INTO Mapping values('贷款','MT02','BANK_02');

INSERT INTO Mapping values('贷款','MT06','BANK_02');

INSERT INTO Mapping values('存款','MT03','BANK_03');

INSERT INTO Mapping values('存款','MT07','BANK_03');

INSERT INTO Mapping values('教育储蓄','MT04','BANK_04');

INSERT INTO Mapping values('教育储蓄','MT08','BANK_04');

SELECT CUST_ID,

       CUST_NAME,

       BANK_NO,

       BANK,

max(CASE WHEN B.Code_N='BANK_01' THEN MONEY ELSE '' END) AS 理财,

max(CASE WHEN B.Code_N='BANK_02' THEN MONEY ELSE '' END) AS 贷款,

max(CASE WHEN B.Code_N='BANK_03' THEN MONEY ELSE '' END) AS 存款,

max(CASE WHEN B.Code_N='BANK_04' THEN MONEY ELSE '' END) AS 教育储蓄

FROM CUST_BANK_INFO A

LEFT JOIN MAPPING B ON A.MONEY_TYPE_NO=B.Code_L

group by CUST_ID,CUST_NAME,BANK,BANK_NO

查询结果如下：