oracle转行交流群,oracle多列转行

遇到的问题描述如下：

现有如下数据格式(待转换表)：

month_no kpi_code  011  013  023  018  032

201207   ABCD0001   12   12   12   12   12

201207   ABCD0002   12   12   12   12   12

201207   ABCD0003   12   12   12   12   12

201207   ABCD0004   12   12   12   12   12

201207   ABCD0005   12   12   12   12   12

(列011,013,023,018等实际作为转换后的prov_id并且实际应用中，prov_id不止示例中的五个)

转换为：

month_no  prov_id  kpi_code  kpi_value

201207      011     ABCD0001     12

201207      013     ABCD0001     12

201207      023     ABCD0001     12

201207      018     ABCD0001     12

处理过程：

首先，建三张临时表：

-- Create table 待转换表

create table M_TEST

(

MONTH_NO VARCHAR2(10),

PROV_ID  VARCHAR2(6),

YDCB0001 NUMBER,

YDCB0002 NUMBER,

YDCB0003 NUMBER,

YDCB0004 NUMBER,

YDCB0005 NUMBER,

YDCB0006 NUMBER

)

tablespace USERS

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

-- Create table 转换后表

create table M_TEST_T

(

MONTH_ID  VARCHAR2(10),

PROV_ID   VARCHAR2(6),

KPI_CODE  VARCHAR2(10),

KPI_VALUE NUMBER

)

tablespace USERS

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

-- Create table 日志表

create table M_LOG

(

PROC_NAME VARCHAR2(30),

PROV_ID   VARCHAR2(10),

TEST_DATE DATE

)

tablespace USERS

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

next 1M

minextents 1

maxextents unlimited

);

---转换前至转换后的处理过程

----创建存储过程

create or replace procedure p_m_test_t(month_id varchar2,

prov_no  varchar2,

retinfo  out varchar2) is

proc_name varchar2(30);

V_SQL     LONG;

BEGIN

proc_name := 'P_M_TEST_T';

V_SQL     := 'INSERT INTO m_test_t'; --结果表

FOR T IN (select column_name

from user_tab_columns

where table_name = 'M_TEST'

and column_id > 2) LOOP

V_SQL := V_SQL || ' SELECT MONTH_NO,PROV_ID,''' || T.COLUMN_NAME ||

''',' || T.COLUMN_NAME || ' FROM m_test where month_no=' ||

month_id ||' and (prov_id = '''||prov_no||''' or ''ALL''='''||prov_no||''') UNION ALL ';

END LOOP;

V_SQL := RTRIM(V_SQL, ' UNION ALL ');

DBMS_OUTPUT.put_line(V_SQL);

EXECUTE IMMEDIATE V_SQL;

COMMIT;

--插入日志表信息

insert into m_log

(proc_name, prov_id, test_date)

values

(proc_name, prov_no, sysdate);

commit;

retinfo := ' 结束 ';

EXCEPTION

WHEN OTHERS THEN

retinfo := ' 失败 ';

END;