ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 行转列与列转行

行转列与列转行

原创 Linux操作系统 作者:space6212 时间:2019-07-21 11:06:01 0 删除 编辑

今天写了可以实现行转列和列转行的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上通过。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/231499/viewspace-63808/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    245
  • 访问量
    166217