Oracle行列转换

--行转列

select count(*) OVER() CNT, RN, STR

  from (select ROWNUM RN,

               SUBSTR(A.STR,

                      INSTR(A.STR, ',', 1, A.N) + 1,

                      INSTR(A.STR, ',', 1, A.N + 1) -

                      INSTR(A.STR, ',', 1, A.N) - 1) STR

          from (select ',a,b,c,d,' STR, ROWNUM N

                  from DUAL

                connect by ROWNUM <= 2000) A) T0

 where T0.STR is not null

 

--列转行

create table tmp_tab_column(table_name  varchar2(30),column_names varchar2(4000));

 

declare

  VC_TAB_NAME varchar2(30);

begin

  for CUR_TB in (select TABLE_NAME

                   from DBA_TABLES

                  where OWNER = 'FM'

                    and ROWNUM < 10) loop

    VC_TAB_NAME := CUR_TB.TABLE_NAME;

    insert into TMP_TAB_COLUMN

      (TABLE_NAME, COLUMN_NAMES)

      (select VC_TAB_NAME,

              '(' ||

              max(SUBSTR(SYS_CONNECT_BY_PATH(T.COLUMN_NAME, ','), 2)) || ')'

         from (select A.COLUMN_NAME, A.COLUMN_ID

                 from DBA_TAB_COLUMNS A

                where A.OWNER = 'FM'

                  and A.TABLE_NAME = VC_TAB_NAME) T

        start with T.COLUMN_ID = 1

       connect by prior T.COLUMN_ID + 1 = T.COLUMN_ID);

    commit;

  end loop;

end;

 

 

SELECT MAX(SUBSTR(SYS_CONNECT_BY_PATH(T.COLUMN_NAME, ','), 2))

  FROM (SELECT COLUMN_NAME, COLUMN_ID

          FROM DBA_TAB_COLUMNS

         WHERE OWNER = 'OCIS'

           AND TABLE_NAME = 'G_KHDA') T

 START WITH T.COLUMN_ID = 1

CONNECT BY PRIOR T.COLUMN_ID + 1 = T.COLUMN_ID