行列转换 oracle

来源：互联网发布：域名交易平台编辑：程序博客网时间：2024/05/17 04:17

经常有朋友问行列转换的问题，
这里举一个例子：
帖子地址：http://topic.csdn.net/u/20070919/10/35d7be36-9992-4a99-a86b-a3269bb50160.html
主要参考了jdsnhan的写法，但是jdsnhan的写法是错误的。下面的列子经过严格测试。

CREATE TABLE test1
(
ID    VARCHAR2(10),
NAME VARCHAR2(100)
);

TRUNCATE TABLE test1;

INSERT INTO test1
     VALUES ('12', '暗暗，得到');

INSERT INTO test1
     VALUES ('12', '大家');

INSERT INTO test1
     VALUES ('12', '认可');

INSERT INTO test1
     VALUES ('13', '认可');

INSERT INTO test1
     VALUES ('13', '很好');

INSERT INTO test1
     VALUES ('bianliang', 'bianliang');

INSERT INTO test1
     VALUES ('bianliang', 'bianliang2');

SELECT *
FROM test1;

SELECT     ID, MAX (SUBSTR ((SYS_CONNECT_BY_PATH (NAME, ', ')), 2)) cola
      FROM (SELECT ID, NAME, ROWNUM rnum,
                   ROW_NUMBER () OVER (PARTITION BY ID ORDER BY ID) rn1
              FROM test1)
START WITH rn1 = 1
CONNECT BY rnum - 1 = PRIOR rnum and id = prior id
GROUP BY ID;