oracle分组结果变成行列,利用sum来实现行列转换-Oracle

利用sum来实现行列转换

原始表如下：  www.2cto.com

预期想要将表中的每一个IDX_CODE分别在查询结果中各显示一列，结果表如下：

建表语句如下：

create table test

(

IDX_DATA_ID   NUMBER(12) not null,

org_no        NUMBER(2),

org_name      VARCHAR2(6),

idx_code      VARCHAR2(12),

stat_cycle    NUMBER(6),

data_value    NUMBER(12),

CHAIN_VALUE   NUMBER(6,2)

)

插入数据：  www.2cto.com

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025889, 1, ‘北京’, ‘ZH001360’, 201210, 100000, 0.00);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025890, 1, ‘北京’, ‘ZH001360’, 201211, 110000, 1.10);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025891, 1, ‘北京’, ‘ZH001360’, 201212, 90000, 0.82);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025892, 1, ‘北京’, ‘ZH001359’, 201210, 200000, 0.00);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025893, 1, ‘北京’, ‘ZH001359’, 201211, 210000, 1.05);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025894, 1, ‘北京’, ‘ZH001359’, 201212, 190000, 0.90);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025895, 2, ‘上海’, ‘ZH001360’, 201210, 100000, 0.00);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025896, 2, ‘上海’, ‘ZH001360’, 201211, 110000, 1.10);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025897, 2, ‘上海’, ‘ZH001360’, 201212, 90000, 0.82);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025898, 2, ‘上海’, ‘ZH001359’, 201210, 200000, 0.00);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025899, 2, ‘上海’, ‘ZH001359’, 201211, 210000, 1.05);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025900, 2, ‘上海’, ‘ZH001359’, 201212, 190000, 0.90);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025901, 3, ‘天津’, ‘ZH001360’, 201210, 100000, 0.00);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025902, 3, ‘天津’, ‘ZH001360’, 201211, 110000, 1.10);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025903, 3, ‘天津’, ‘ZH001360’, 201212, 90000, 0.82);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025904, 3, ‘天津’, ‘ZH001359’, 201210, 200000, 0.00);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025905, 3, ‘天津’, ‘ZH001359’, 201211, 210000, 1.05);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

values (1300025906, 3, ‘天津’, ‘ZH001359’, 201212, 190000, 0.90);

实现预期结果的查询语句如下：

www.2cto.com

SELECT T1.ORG_NAME,

T1.STAT_CYCLE,

SUM(CASE

WHEN T1.IDX_CODE = ‘ZH001359’ THEN

T1.DATA_VALUE

END) AS DV1,

SUM(CASE

WHEN T1.IDX_CODE = ‘ZH001360’ THEN

T1.DATA_VALUE

END) AS DV2,

CAST(SUM(CASE

WHEN T1.IDX_CODE = ‘ZH001359’ THEN

T1.CHAIN_VALUE

END) AS NUMBER(6, 2)) AS DV3

FROM TEST T1

WHERE T1.IDX_CODE IN (‘ZH001359’, ‘ZH001360’)

AND T1.STAT_CYCLE >= ‘201201’

AND T1.STAT_CYCLE <= ‘201212’

AND T1.ORG_NO = ‘1’

GROUP BY T1.STAT_CYCLE, T1.ORG_NAME

ORDER BY STAT_CYCLE DESC;