ITPub博客

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

oracle 列转行

原创 Linux操作系统 作者:exbean 时间:2013-08-07 20:54:35 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-12-25

  • 博文量
    22
  • 访问量
    147924