oracle经典实用行列转换

本文介绍如何使用SQL将数据从行转列及从列转行的方法,包括使用DECODE和CASE语句实现不同指标代码的数据聚合,并展示具体操作步骤。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

/*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;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值