20210605:数据库操作_数据转置显示

最新推荐文章于 2024-03-18 17:59:28 发布

szc_1985

最新推荐文章于 2024-03-18 17:59:28 发布

阅读量184

点赞数

分类专栏：数据库办公记录语句文章标签：数据库

本文链接：https://blog.csdn.net/szc_1985/article/details/117604935

版权

数据库办公记录语句专栏收录该内容

40 篇文章 1 订阅

订阅专栏

可以根据前台导入数据库的最多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(d.name(+),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(d.name(+),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 ;