oracle经典实用行列转换

/*create table T_SAFETY_RUN_TEST
(
  SAFETY_RUN_ID INTEGER not null,
  ORG_ID        INTEGER,
  KEY_DATE_ID   VARCHAR2(16),
  ITEM_CODE     VARCHAR2(32),
  ITEM_VALUE    NUMBER(21,6)
);
insert into T_SAFETY_RUN_TEST (SAFETY_RUN_ID, ORG_ID, KEY_DATE_ID, ITEM_CODE, ITEM_VALUE)
values (7967, 10, '201010', '10006', 3020);
insert into T_SAFETY_RUN_TEST (SAFETY_RUN_ID, ORG_ID, KEY_DATE_ID, ITEM_CODE, ITEM_VALUE)
values (10130, 9, '201010', '10006', 3145);
insert into T_SAFETY_RUN_TEST (SAFETY_RUN_ID, ORG_ID, KEY_DATE_ID, ITEM_CODE, ITEM_VALUE)
values (12703, 9, '201010', '10010', 1200000);
insert into T_SAFETY_RUN_TEST (SAFETY_RUN_ID, ORG_ID, KEY_DATE_ID, ITEM_CODE, ITEM_VALUE)
values (16899, 10, '201010', '10010', 600000);
insert into T_SAFETY_RUN_TEST (SAFETY_RUN_ID, ORG_ID, KEY_DATE_ID, ITEM_CODE, ITEM_VALUE)
values (23176, 9, '201010', '20001', 1200000);
commit;*/

--行转列--
--行基础表结构
select * from t_safety_run_test t;
--方法1：

select t.org_id as 组织机构,
       t.key_date_id as 时间,
       max(decode(t.item_code, '10006', t.item_value, 0)) as 发电厂用电量,
       max(decode(t.item_code, '10006', t.tongqi, 0)) as 同期,
       max(decode(t.item_code, '10006', t.leiji, 0)) as 月累计,
       max(decode(t.item_code, '10010', t.item_value, 0)) as 装机容量,
       max(decode(t.item_code, '10010', t.tongqi, 0)) as 同期,
       max(decode(t.item_code, '10010', t.leiji, 0)) as 月累计,
       max(decode(t.item_code, '20001', t.item_value, 0)) as 供热设备容量,
       max(decode(t.item_code, '20001', t.tongqi, 0)) as 同期,
       max(decode(t.item_code, '20001', t.leiji, 0)) as 月累计
  from t_safety_run_test t
 group by t.org_id, t.key_date_id
 order by t.org_id;

--方法2：
select t.org_id as 组织机构,
       t.key_date_id as 时间,
       max(case t.item_code
             when '10006' then
              t.item_value
             else
              0
           end) as 发电厂用电量,
       max(case t.item_code
             when '10006' then
              t.tongqi
             else
              0
           end) as 同期,
       max(case t.item_code
             when '10006' then
              t.leiji
             else
              0
           end) as 月累计,
       max(case t.item_code
             when '10010' then
              t.item_value
             else
              0
           end) as 装机容量,
       max(case t.item_code
             when '10010' then
              t.tongqi
             else
              0
           end) as 同期,
       max(case t.item_code
             when '10010' then
              t.leiji
             else
              0
           end) as 月累计,
       max(case t.item_code
             when '20001' then
              t.item_value
             else
              0
           end) as 供热设备容量,
       max(case t.item_code
             when '20001' then
              t.tongqi
             else
              0
           end) as 同期,
       max(case t.item_code
             when '20001' then
              t.leiji
             else
              0
           end) as 月累计
  from t_safety_run_test t
 group by org_id, key_date_id
 order by org_id;

--指标代码行不进行转换
select t.org_id as 组织机构,
       t.key_date_id as 时间,
       max(decode(t.item_code, '10006', t.item_code, 0)) as 指标代码,
       max(decode(t.item_code, '10006', t.item_value, 0)) as 发电厂用电量,
       max(decode(t.item_code, '10006', t.tongqi, 0)) as 同期,
       max(decode(t.item_code, '10006', t.leiji, 0)) as 月累计,
       max(decode(t.item_code, '10010', t.item_code, 0)) as 指标代码,
       max(decode(t.item_code, '10010', t.item_value, 0)) as 装机容量,
       max(decode(t.item_code, '10010', t.tongqi, 0)) as 同期,
       max(decode(t.item_code, '10010', t.leiji, 0)) as 月累计,
       max(decode(t.item_code, '20001', t.item_code, 0)) as 指标代码,
       max(decode(t.item_code, '20001', t.item_value, 0)) as 供热设备容量,
       max(decode(t.item_code, '20001', t.tongqi, 0)) as 同期,
       max(decode(t.item_code, '20001', t.leiji, 0)) as 月累计
  from t_safety_run_test t
 group by t.org_id, t.key_date_id
 order by t.org_id;

--列转行--

/*create table t_col_row as

select t.org_id ,
       t.key_date_id,
       max(decode(t.item_code,'10006',t.item_value,0)) as cydl,
       max(decode(t.item_code,'10006',t.tongqi,0)) as cydltq,
       max(decode(t.item_code,'10006',t.leiji,0)) as cydlylj,
       max(decode(t.item_code,'10010',t.item_value,0)) as zjrl,
       max(decode(t.item_code,'10010',t.tongqi,0)) as zjrltq,
       max(decode(t.item_code,'10010',t.leiji,0)) as zjrlylj,
       max(decode(t.item_code,'20001',t.item_value,0)) as grrl,
       max(decode(t.item_code,'20001',t.tongqi,0)) as grrltq,
       max(decode(t.item_code,'20001',t.leiji,0)) as grrlylj
       from
 t_safety_run_test t
 group by t.org_id,t.key_date_id
 order by t.org_id;*/

--列基础表结构
select * from t_col_row t;
--方法1：
select t.org_id as 组织机构,
       t.key_date_id as 时间,
       '10006' as 指标代码,
       t.cydl as 本期,
       t.cydltq as 同期,
       t.cydlylj as 月累计
  from t_col_row t
union all
select t.org_id as 组织机构,
       t.key_date_id as 时间,
       '10010' as 指标代码,
       t.zjrl as 本期,
       t.zjrltq as 同期,
       t.zjrlylj as 月累计
  from t_col_row t
union all
select t.org_id as 组织机构,
       t.key_date_id as 时间,
       '20001' as 指标代码,
       t.grrl as 本期,
       t.grrltq as 同期,
       t.grrlylj as 月累计
  from t_col_row t;

--方法2：
select tt.org_id as 组织机构,
       tt.key_date_id as 时间,
       decode(rn, 1, '10006', 2, '10010', 3, '20001') as 指标代码,
       decode(rn, 1, cydl, 2, zjrl, 3, grrl) as 本期,
       decode(rn, 1, cydltq, 2, zjrltq, 3, grrltq) as 同期,
       decode(rn, 1, cydlylj, 2, zjrlylj, 3, grrlylj) as 月累计
  from (select *
          from t_col_row t,
               (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 3) r) tt;

select tt.org_id as 组织机构,
       tt.key_date_id as 时间,
       decode(rn, 1, '10006', 2, '10010', 3, '20001') as 指标代码,
       decode(rn, 1, cydl, 2, cydltq, 3, cydlylj) as 指标值
  from (select *
          from t_col_row t,
               (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 3) r) tt;

select tt.org_id as 组织机构,
       tt.key_date_id as 时间,
       decode(rn,
              1,
              '10006',
              2,
              '10010',
              3,
              '20001',
              4,
              '100061',
              5,
              '100101',
              6,
              '200011',
              7,
              '1000611',
              8,
              '1001011',
              9,
              '2000111') as 指标代码,
       decode(rn,
              1,
              cydl,
              2,
              cydltq,
              3,
              cydlylj,
              4,
              zjrl,
              5,
              zjrltq,
              6,
              zjrlylj,
              7,
              grrl,
              8,
              grrltq,
              9,
              grrlylj) as 指标值
  from (select *
          from t_col_row t,
               (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 9) r) tt;
 
--方法3：

/*CREATE TYPE item_pair AS OBJECT(item_col VARCHAR2(10),item_code VARCHAR2(10),item_value VARCHAR2(10)); --创建对象

CREATE TYPE item_varr AS VARRAY(8) OF item_pair;--创建集合*/

select * from t_col_row t;
--列基础表

select org_id as 组织机构,
       key_date_id as 时间,
       t.item_code as 指标代码,
       t.item_value as 指标值
from t_col_row,
     TABLE(item_varr(item_pair('CYDL','10006',t_col_row.cydl),                                        
                     item_pair('ZJRL','10010',t_col_row.zjrl),
                     item_pair('GRRL','20001',t_col_row.grrl))) t
ORDER BY 1, 2;