oracle 列转行

CREATE TABLE t_row_str(

ID INT,

col VARCHAR2(10));

INSERT INTO t_row_str VALUES(1,'a');

INSERT INTO t_row_str VALUES(1,'b');

INSERT INTO t_row_str VALUES(1,'c');

INSERT INTO t_row_str VALUES(1,'d');

INSERT INTO t_row_str VALUES(1,'e');

INSERT INTO t_row_str VALUES(2,'a');

INSERT INTO t_row_str VALUES(2,'d');

INSERT INTO t_row_str VALUES(2,'e');

INSERT INTO t_row_str VALUES(3,'c');

INSERT INTO t_row_str VALUES(4,'c');

COMMIT;

SELECT * FROM t_row_str;

---以下的方法需要手工的构造连接字符串，因此适合列类型不多的情况。

SELECT id, str

  FROM (SELECT id,

               row_number() over(PARTITION BY id ORDER BY col) AS rn,

               col || lead(',' || col, 1) over(PARTITION BY id ORDER BY col)||

               lead(',' || col, 2) over(PARTITION BY id ORDER BY col) ||

               lead(',' || col, 3) over(PARTITION BY id ORDER BY col) AS str

          FROM t_row_str)

WHERE rn = 1

ORDER BY 1;

-----------------------

-----使用内置 sys_connect_by_path函数--------可以一次处理

SELECT t.id id,/*rn, */MAX(substr(sys_connect_by_path(t.col, ','), 2) )str

  FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn

          FROM t_row_str) t

START WITH rn = 1

CONNECT BY rn = PRIOR rn + 1

       AND id = PRIOR id

GROUP BY t.id;

--------------------------------------

SELECT * FROM t_row_str order by id,col

SELECT id,

               row_number() over(PARTITION BY id ORDER BY col) AS rn,

               col || lead(',' || col, 1) over(PARTITION BY id ORDER BY col) ||

               lead(',' || col, 2) over(PARTITION BY id ORDER BY col) ||

               lead(',' || col, 3) over(PARTITION BY id ORDER BY col) AS str

          FROM t_row_str;

          

--SELECT t.id id,rn, MAX(substr(sys_connect_by_path(t.col, ','), 2) )str

SELECT t.id id,rn, sys_connect_by_path(t.col, ',') str

  FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn

          FROM t_row_str) t

START WITH rn = 1

CONNECT BY rn = PRIOR rn + 1

       AND id = PRIOR id

GROUP BY t.id;

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

转载于:http://blog.itpub.net/9236282/viewspace-767991/