oracle 行转列与列转行

习5477

行转列和列转行的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上通过。