思路:先建一个行列转换的函数,然后调用。SQL> create table t1(id varchar2(2),name varchar2(10));表已创建。SQL> BEGIN
2 INSERT INTO T1 VALUES('01','A');
3 INSERT INTO T1 VALUES('02','B');
4 INSERT INTO T1 VALUES('01','C');
5 INSERT INTO T1 VALUES('01','D');
6 INSERT INTO T1 VALUES('02','E');
7 COMMIT;
8 END;
9 /PL/SQL 过程已成功完成。SQL> SELECT * FROM T1;ID NAME
-- ----------
01 A
02 B
01 C
01 D
02 E已选择5行。SQL> CREATE OR REPLACE FUNCTION TO_LINE(
2 STR1 IN VARCHAR2
3 )
4 RETURN VARCHAR2
5 AS
6 STR2 VARCHAR2(255);
7 BEGIN
8 STR2:='';
9 FOR X IN (SELECT NAME FROM T1 WHERE ID=STR1) LOOP
10 STR2:=STR2||','||X.NAME;
11 END LOOP;
12 STR2:=SUBSTR(STR2,2);
13 RETURN STR2;
14 END;
15 /函数已创建。SQL> SELECT ID,TO_LINE(ID) NAME FROM (SELECT ID FROM T1 GROUP BY ID);ID NAME
-- --------------------
01 A,C,D
02 B,E已选择2行。
2 INSERT INTO T1 VALUES('01','A');
3 INSERT INTO T1 VALUES('02','B');
4 INSERT INTO T1 VALUES('01','C');
5 INSERT INTO T1 VALUES('01','D');
6 INSERT INTO T1 VALUES('02','E');
7 COMMIT;
8 END;
9 /PL/SQL 过程已成功完成。SQL> SELECT * FROM T1;ID NAME
-- ----------
01 A
02 B
01 C
01 D
02 E已选择5行。SQL> CREATE OR REPLACE FUNCTION TO_LINE(
2 STR1 IN VARCHAR2
3 )
4 RETURN VARCHAR2
5 AS
6 STR2 VARCHAR2(255);
7 BEGIN
8 STR2:='';
9 FOR X IN (SELECT NAME FROM T1 WHERE ID=STR1) LOOP
10 STR2:=STR2||','||X.NAME;
11 END LOOP;
12 STR2:=SUBSTR(STR2,2);
13 RETURN STR2;
14 END;
15 /函数已创建。SQL> SELECT ID,TO_LINE(ID) NAME FROM (SELECT ID FROM T1 GROUP BY ID);ID NAME
-- --------------------
01 A,C,D
02 B,E已选择2行。
如果题目中id的个数不是很多的话,
.....
那就试试吧:SQL> ED
已写入文件 afiedt.buf 1 SELECT ID,PRE||','||NAME||','||NEXT NAME FROM
2 (
3 SELECT ID,LAG(NAME) OVER (ORDER BY ROWNUM) PRE,NAME,LEAD(NAME) OVER (ORDER BY ROWNUM) NEXT
4 FROM T1
5 WHERE ID='01'
6 ) C
7 WHERE C.PRE IS NOT NULL AND ROWNUM=1
8 union
9 SELECT ID,PRE||','||NAME||','||NEXT NAME FROM
10 (
11 SELECT ID,LAG(NAME) OVER (ORDER BY ROWNUM) PRE,NAME,LEAD(NAME) OVER (ORDER BY ROWNUM) NEXT
12 FROM T1
13 WHERE ID='02'
14 ) C
15* WHERE C.PRE IS NOT NULL AND ROWNUM=1
SQL> /ID NAME
-- --------------------
01 A,C,D
02 B,E,已选择2行。
太多的如果了,不具普遍性
....正确的思路应该还是用函数转换。
解决方法请见
http://dev.csdn.net/develop/article/72/72863.shtm
涉及到了多表的问题只要原理一样也可以使用,请见
http://blog.csdn.net/heyixiang/archive/2005/07/06/415444.aspx