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