可以根据前台导入数据库的最多200列数据，根据第一行标题将数据转置成竖版：
时间 | 区县 | 指标名称1 | 指标名称2 | 指标名称3 | 指标名称4 | 指标名称5 | 指标名称6 | 指标名称7 | 指标名称8 |
201901 | 区县1 | 92 | 91 | 82 | 42 | 66 | 60 | 7 | 23 |
201901 | 区县2 | 29 | 12 | 4 | 75 | 19 | 50 | 12 | 82 |
201901 | 区县3 | 46 | 86 | 69 | 2 | 81 | 40 | 18 | 12 |
201901 | 区县4 | 18 | 6 | 11 | 91 | 76 | 90 | 74 | 2 |
201901 | 区县5 | 80 | 71 | 75 | 51 | 32 | 6 | 36 | 38 |
201901 | 区县6 | 34 | 25 | 50 | 64 | 37 | 47 | 28 | 56 |
201901 | 区县7 | 37 | 53 | 85 | 72 | 26 | 81 | 98 | 42 |
201901 | 区县8 | 77 | 47 | 35 | 97 | 83 | 51 | 44 | 93 |
201901 | 区县9 | 7 | 75 | 22 | 38 | 29 | 37 | 79 | 71 |
会统一转置为这样
指标名 | 月份 | 区县 | 数值 | 排名 |
指标名称1 | 201901 | 区县1 | 4 | 1 |
指标名称1 | 201901 | 区县2 | 34 | 2 |
指标名称1 | 201901 | 区县3 | 95 | 3 |
指标名称1 | 201901 | 区县4 | 91 | 4 |
指标名称1 | 201901 | 区县5 | 67 | 5 |
指标名称1 | 201901 | 区县6 | 60 | 6 |
指标名称1 | 201901 | 区县7 | 72 | 7 |
指标名称1 | 201901 | 区县8 | 19 | 8 |
指标名称1 | 201901 | 区县9 | 33 | 9 |
代码如下：
create or replace procedure biller533.shzc_202106_cwbb_jf_drhangd_mx
(p_userid varchar2,p_rq varchar2, p_cursor in out Results.ref_cursor_type)
as
v_rq VARCHAR2(80);
v_sqd VARCHAR2(80);
v_zbpm VARCHAR2(80);
v_sql VARCHAR2(30000);
v_hd VARCHAR2(100);
SQL_STRING VARCHAR2(6000);
SQLSTMT0 VARCHAR2(32760);
SQLSTMT1 VARCHAR2(32760);
V_ZMLX VARCHAR2(20);
V_ZMLX_SZ VARCHAR2(20);
v_zdyf varchar2(10);
v_zdyf_next varchar2(10);
v_zdyf_d date;
v_zdyf_qn varchar2(10); ---去年
begin
v_rq := substr( to_char(sysdate,'yyyymmdd'),1,6);
v_zdyf_d:=to_date(v_rq,'yyyymm'); ---转成月日期
v_zdyf_qn:=to_char(add_months(v_zdyf_d,-12),'yyyymm'); ---转成去年字符
v_zdyf:=to_char(add_months(v_zdyf_d,-1),'yyyymmdd');
v_zdyf_next:=to_char(add_months(v_zdyf_d,1),'yyyymmdd');
----基础数据——行固定
zhyw.fan_drop_retable(upper('cwbb_jfbbdr_hang_drcc_sx'),'SHZC');
SQL_STRING := 'create table SHZC.cwbb_jfbbdr_hang_drcc_sx as
select * from shzc.cwbb_jfbbdr_hang_drcc a
where nvl(a.经营单位,''全市排名'') <>''全市排名''
AND nvl(a.月份,''时间'' )<>''时间'' ' ;
execute immediate (SQL_STRING);
----先提取表格字段名
zhyw.fan_drop_retable(upper('cwbb_jfbbdr_hang_yue_sx'),'SHZC');
SQL_STRING := 'create table SHZC.cwbb_jfbbdr_hang_yue_sx as
select a.COLUMN_NAME from
(select a.COLUMN_NAME from ALL_TAB_COLUMNS a
where a.TABLE_NAME =upper(''cwbb_jfbbdr_hang_drcc'')
and a.OWNER=''SHZC'' and a.COLUMN_ID>=3
and a.COLUMN_NAME not in (''USER_ID'',''IN_TIME'',''排名'')
order by a.COLUMN_ID ) a ' ;
execute immediate (SQL_STRING);
----先提取表格字段 编码 对应数据
zhyw.fan_drop_retable(upper('cwbb_jfbbdr_hang_yue_sxa'),'SHZC');
SQL_STRING := 'create table SHZC.cwbb_jfbbdr_hang_yue_sxa as
select 字段1,字段2,字段3,字段4,字段5,字段6,字段7,字段8,字段9,字段10,字段11,
字段12,字段13,字段14,字段15,字段16,字段17,字段18,字段19,字段20,字段21,字段22,
字段23,字段24,字段25,字段26,字段27,字段28,字段29,字段30,字段31,字段32,字段33,
字段34,字段35,字段36,字段37,字段38,字段39,字段40,字段41,字段42,字段43,字段44,
字段45,字段46,字段47,字段48,字段49,字段50,字段51,字段52,字段53,字段54,字段55,
字段56,字段57,字段58,字段59,字段60,字段61,字段62,字段63,字段64,字段65,字段66,
字段67,字段68,字段69,字段70,字段71,字段72,字段73,字段74,字段75,字段76,字段77,
字段78,字段79,字段80,字段81,字段82,字段83,字段84,字段85,字段86,字段87,字段88,
字段89,字段90,字段91,字段92,字段93,字段94,字段95,字段96,字段97,字段98,字段99,
字段100,字段101,字段102,字段103,字段104,字段105,字段106,字段107,字段108,字段109,
字段110,字段111,字段112,字段113,字段114,字段115,字段116,字段117,字段118,字段119,
字段120,字段121,字段122,字段123,字段124,字段125,字段126,字段127,字段128,字段129,
字段130,字段131,字段132,字段133,字段134,字段135,字段136,字段137,字段138,字段139,
字段140,字段141,字段142,字段143,字段144,字段145,字段146,字段147,字段148,字段149,
字段150,字段151,字段152,字段153,字段154,字段155,字段156,字段157,字段158,字段159,
字段160,字段161,字段162,字段163,字段164,字段165,字段166,字段167,字段168,字段169,
字段170,字段171,字段172,字段173,字段174,字段175,字段176,字段177,字段178,字段179,
字段180,字段181,字段182,字段183,字段184,字段185,字段186,字段187,字段188,字段189,
字段190,字段191,字段192,字段193,字段194,字段195,字段196,字段197,字段198,字段199,字段200
from shzc.cwbb_jfbbdr_hang_drcc a
where a.月份=''时间'' ' ;
execute immediate (SQL_STRING);
zhyw.fan_drop_retable('SHZC_JFBB_QXMC_HANG_ZD','SHZC');
DECLARE
CURSOR ZMLX IS
select COLUMN_NAME from SHZC.cwbb_jfbbdr_hang_yue_sx ;
BEGIN
OPEN ZMLX;
SQLSTMT0 := 'create table SHZC.SHZC_JFBB_QXMC_HANG_ZD
storage (initial 8m next 3m pctincrease 0) as
';
LOOP
FETCH ZMLX INTO V_ZMLX;
EXIT WHEN ZMLX%NOTFOUND;
SQLSTMT0 := SQLSTMT0 || 'select '''||V_ZMLX ||''' 字段名,
max( nvl(a.'||V_ZMLX ||',''0'')) 字段指标
from shzc.cwbb_jfbbdr_hang_yue_sxa a
where rownum=1
UNION ALL ';
END LOOP;
SQLSTMT0 := SQLSTMT0 || ' ';
--语句截取组成完整语句
SQLSTMT1 := SUBSTR(SQLSTMT0 , 1 , length( SQLSTMT0 )-11) ;
EXECUTE IMMEDIATE (SQLSTMT1);
end;
-------字段名数据
zhyw.fan_drop_retable('SHZC_JFBB_QXMC_HANG','SHZC');
DECLARE
CURSOR ZMLX IS
select 字段名 from SHZC.SHZC_JFBB_QXMC_HANG_ZD a where a.字段指标 <>'0' ;
BEGIN
OPEN ZMLX;
SQLSTMT0 := 'create table SHZC.SHZC_JFBB_QXMC_HANG
storage (initial 8m next 3m pctincrease 0) as
';
LOOP
FETCH ZMLX INTO V_ZMLX;
EXIT WHEN ZMLX%NOTFOUND;
SQLSTMT0 := SQLSTMT0 || 'select A.月份,a.经营单位 区县,'''||V_ZMLX ||''' 字段名,
max( nvl(a.'||V_ZMLX ||',''0'')) 指标
from shzc.cwbb_jfbbdr_hang_drcc_sx a
group by A.月份,A.经营单位
UNION ALL ';
END LOOP;
SQLSTMT0 := SQLSTMT0 || ' ';
--语句截取组成完整语句
SQLSTMT1 := SUBSTR(SQLSTMT0 , 1 , length( SQLSTMT0 )-11) ;
EXECUTE IMMEDIATE (SQLSTMT1);
end;
------全市排名数据
zhyw.fan_drop_retable(upper('cwbb_jfbbdr_hang_drcc_pm'),'SHZC');
SQL_STRING := 'create table SHZC.cwbb_jfbbdr_hang_drcc_pm as
select * from shzc.cwbb_jfbbdr_hang_drcc a
where nvl(a.经营单位,''全市'') =''全市排名'' ' ;
execute immediate (SQL_STRING);
zhyw.fan_drop_retable('SHZC_JFBB_QXMC_HANG_PM','SHZC');
DECLARE
CURSOR ZMLX IS
select 字段名 from SHZC.SHZC_JFBB_QXMC_HANG_ZD a where a.字段指标 <>'0' ;
BEGIN
OPEN ZMLX;
SQLSTMT0 := 'create table SHZC.SHZC_JFBB_QXMC_HANG_PM
storage (initial 8m next 3m pctincrease 0) as
';
LOOP
FETCH ZMLX INTO V_ZMLX;
EXIT WHEN ZMLX%NOTFOUND;
SQLSTMT0 := SQLSTMT0 || 'select substr(a.经营单位,1,2) 区县,a.月份, '''||V_ZMLX ||''' 字段名,
max( to_number(nvl(a.'||V_ZMLX ||',''0''))) 排名
from shzc.cwbb_jfbbdr_hang_drcc_pm a
group by substr(a.经营单位,1,2),a.月份
UNION ALL ';
END LOOP;
SQLSTMT0 := SQLSTMT0 || ' ';
--语句截取组成完整语句
SQLSTMT1 := SUBSTR(SQLSTMT0 , 1 , length( SQLSTMT0 )-11) ;
EXECUTE IMMEDIATE (SQLSTMT1);
end;
----
SQLSTMT1 := 'update SHZC.SHZC_JFBB_QXMC_HANG a set a.指标=0
where a.指标 in (''-0'',''-0.00'',''全省无数据'') ';
EXECUTE IMMEDIATE (SQLSTMT1);
----结果数据展现
zhyw.fan_drop_retable('SHZC_JFBB_QXMC_HANG_'||v_rq||'_JG','SHZC');
SQLSTMT1 := 'create table SHZC.SHZC_JFBB_QXMC_HANG_' || v_rq ||'_JG AS
select d.字段指标 指标编码,a.月份,a.区县,a.指标,
nvl(to_number(b.排名),case when a.区县 in (select c.名称 from SHZC.cwbb_bbzx_qx_pm_bm_mx c where c.区县市区=''区县'') then
row_number() over (partition by a.月份,d.字段指标 order by to_number(a.指标) desc ) end) 排名
from SHZC.SHZC_JFBB_QXMC_HANG a ,
SHZC.SHZC_JFBB_QXMC_HANG_PM b,
SHZC.SHZC_JFBB_QXMC_HANG_ZD d
where a.月份=b.月份(+)
and a.字段名=b.字段名(+)
and a.区县=b.区县(+)
and a.字段名=d.字段名(+)
and a.区县 in (select c.名称 from SHZC.cwbb_bbzx_qx_pm_bm_mx c where c.区县市区=''区县'')
and a.指标 is not null
union all
select d.字段指标 指标编码,a.月份,a.区县,a.指标,
nvl(to_number(b.排名),case when a.区县 in (select c.名称 from SHZC.cwbb_bbzx_qx_pm_bm_mx c where c.区县市区=''区县'') then
row_number() over (partition by a.月份,d.字段指标 order by to_number(a.指标) desc ) end) 排名
from SHZC.SHZC_JFBB_QXMC_HANG a ,
SHZC.SHZC_JFBB_QXMC_HANG_PM b,
SHZC.SHZC_JFBB_QXMC_HANG_ZD d
where a.月份=b.月份(+)
and a.字段名=b.字段名(+)
and a.区县=b.区县(+)
and a.字段名=d.字段名(+)
and a.区县 not in (select c.名称 from SHZC.cwbb_bbzx_qx_pm_bm_mx c where c.区县市区=''区县'')
and a.指标 is not null ';
EXECUTE IMMEDIATE (SQLSTMT1);
----写入存档表，清除导入数据
SQLSTMT1 := 'insert into SHZC.SHZC_JFBB_QXMC_HANG_JGCD
select a.*,'''||p_userid||''' user_id ,sysdate in_time
from SHZC.SHZC_JFBB_QXMC_HANG_'||v_rq||'_JG a ';
EXECUTE IMMEDIATE (SQLSTMT1);
SQLSTMT1 := 'delete shzc.cwbb_jfbbdr_hang_drcc a ';
EXECUTE IMMEDIATE (SQLSTMT1);
commit;
v_sql:='
select a.* from
(select c.字段指标 指标编码,a.月份,a.区县,a.指标,
nvl(to_number(b.排名),case when a.区县 in (select c.名称 from SHZC.cwbb_bbzx_qx_pm_bm_mx c where c.区县市区=''区县'') then
row_number() over (partition by a.月份,c.字段指标 order by to_number(a.指标) desc ) end) 排名,d.county_id
from SHZC.SHZC_JFBB_QXMC_HANG a ,
SHZC.SHZC_JFBB_QXMC_HANG_PM b,
SHZC.SHZC_JFBB_QXMC_HANG_ZD c,
(select * from zhyw.rpt_county d where d.county_id not in (''1'',''0'',''A'',''zbtyqd'') ) d
where a.月份=b.月份(+)
and a.字段名=b.字段名(+)
and a.区县=b.区县(+)
and a.字段名=c.字段名(+)
and substr(a.区县,1,2)=substr((+),1,2)
and a.指标 is not null
and a.区县 in (select c.名称 from SHZC.cwbb_bbzx_qx_pm_bm_mx c where c.区县市区=''区县'')
union all
select c.字段指标 指标编码,a.月份,a.区县,a.指标,
nvl(to_number(b.排名),case when a.区县 in (select c.名称 from SHZC.cwbb_bbzx_qx_pm_bm_mx c where c.区县市区=''区县'') then
row_number() over (partition by a.月份,c.字段指标 order by to_number(a.指标) desc ) end) 排名,d.county_id
from SHZC.SHZC_JFBB_QXMC_HANG a ,
SHZC.SHZC_JFBB_QXMC_HANG_PM b,
SHZC.SHZC_JFBB_QXMC_HANG_ZD c,
(select * from zhyw.rpt_county d where d.county_id not in (''1'',''0'',''A'',''zbtyqd'') ) d
where a.月份=b.月份(+)
and a.字段名=b.字段名(+)
and a.区县=b.区县(+)
and a.字段名=c.字段名(+)
and substr(a.区县,1,2)=substr((+),1,2)
and a.指标 is not null
and a.区县 not in (select c.名称 from SHZC.cwbb_bbzx_qx_pm_bm_mx c where c.区县市区=''区县'')) a
order by a.指标编码,a.月份,a.county_id,a.区县 ' ;
open p_cursor for v_sql;
end ;本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
