oracle经典实用行列转换

最新推荐文章于 2022-02-09 15:16:19 发布

原创最新推荐文章于 2022-02-09 15:16:19 发布 · 442 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #date #insert #pair #integer #table

05数据库同时被 2 个专栏收录

8 篇文章

订阅专栏

02ETL设计与开发

3 篇文章

订阅专栏

本文介绍如何使用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;