今天寫了可以實現行轉列和列轉行的SQL。

--行轉列
SQL> SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME='T1';

COLUMN_NAME
------------------------------
ID
N1
N2
N3

SQL>
SQL> SELECT SUBSTR(MAX(SYS_CONNECT_BY_PATH(COLUMN_NAME,:P_SPLIT)),2) NEW_COL FROM
2 (
3 SELECT COLUMN_NAME,ROWNUM RN FROM USER_TAB_COLUMNS WHERE TABLE_NAME='T1'
4 )
5 START WITH RN=1
6 CONNECT BY ROWNUM=RN;

NEW_COL
--------------------------------------------------------------------------------
ID,N1,N2,N3

--主要用根據rownum構造層次關係，然後用SYS_CONNECT_BY_PATH實現。

--列轉行
/*
列轉行與根據執行分隔符拆分字串功能類似，這裡用拆分字串功能來說明。
*/
--首先測試分隔符是單個字串
SQL> EXEC :STR:='AS,BDD,CGH,D'

PL/SQL procedure successfully completed
STR
---------
AS,BDD,CGH,D

SQL> EXEC :P_SPLIT:=','

PL/SQL procedure successfully completed
P_SPLIT
---------
,

SQL>
SQL> SELECT DECODE(SIGN(INSTR(:STR, :P_SPLIT)),
2 1,
3 SUBSTR(:STR, --字串
4 DECODE(ROWNUM,
5 1,
6 1,
7 INSTR(:STR, :P_SPLIT, 1, ROWNUM - 1) +
8 LENGTH(:P_SPLIT)), --position
9 DECODE(ROWNUM,
10 1,
11 INSTR(:STR, :P_SPLIT, 1, 1) - 1,
12 ((LENGTH(:STR) -
13 LENGTH(REPLACE(:STR, :P_SPLIT, NULL))) /
14 LENGTH(:P_SPLIT)) + 1,
15 LENGTH(:STR),
16 INSTR(:STR, :P_SPLIT, 1, ROWNUM) -
17 INSTR(:STR, :P_SPLIT, 1, ROWNUM - 1) -
18 LENGTH(:P_SPLIT))),
19 :STR) NEW_COL --substring_length
20 FROM DUAL
21 CONNECT BY ROWNUM <= ((LENGTH(:STR) - LENGTH(REPLACE(:STR, :P_SPLIT, NULL))) /
22 LENGTH(:P_SPLIT)) + 1;

NEW_COL
--------------------------------------------------------------------------------
AS
BDD
CGH
D

--可以看到結果已經根據都好被分成4行了
--接著測試分隔符是任意長字元
SQL> EXEC :STR:='AS||BDD||CGH||D'

PL/SQL procedure successfully completed
STR
---------
AS||BDD||CGH||D

SQL> EXEC :P_SPLIT:='||'

PL/SQL procedure successfully completed
P_SPLIT
---------
||

SQL> SELECT DECODE(SIGN(INSTR(:STR, :P_SPLIT)),
2 1,
3 SUBSTR(:STR, --字串
4 DECODE(ROWNUM,
5 1,
6 1,
7 INSTR(:STR, :P_SPLIT, 1, ROWNUM - 1) +
8 LENGTH(:P_SPLIT)), --position
9 DECODE(ROWNUM,
10 1,
11 INSTR(:STR, :P_SPLIT, 1, 1) - 1,
12 ((LENGTH(:STR) -
13 LENGTH(REPLACE(:STR, :P_SPLIT, NULL))) /
14 LENGTH(:P_SPLIT)) + 1,
15 LENGTH(:STR),
16 INSTR(:STR, :P_SPLIT, 1, ROWNUM) -
17 INSTR(:STR, :P_SPLIT, 1, ROWNUM - 1) -
18 LENGTH(:P_SPLIT))),
19 :STR) NEW_COL --substring_length
20 FROM DUAL
21 CONNECT BY ROWNUM <= ((LENGTH(:STR) - LENGTH(REPLACE(:STR, :P_SPLIT, NULL))) /
22 LENGTH(:P_SPLIT)) + 1;

NEW_COL
--------------------------------------------------------------------------------
AS
BDD
CGH
D

--結果是正確的。

以上測試在9201上透過。

行轉列與列轉行

相關文章