select t.rank, t.Name from t_menu_item t;

?
??? 10 CLARK
??? 10 KING
??? 10 MILLER
??? 20 ADAMS
??? 20 FORD
??? 20 JONES
??? 20 SCOTT
??? 20 SMITH
??? 30 ALLEN
??? 30 BLAKE
??? 30 JAMES
??? 30 MARTIN
??? 30 TURNER
??? 30 WARD

--------------------------------
我们通过 10g 所提供的 WMSYS.WM_CONCAT 函数即可以完成 行转列的效果

???

select t.rank, WMSYS.WM_CONCAT(t.Name) TIME From t_menu_item t GROUP BY t.rank;

?

DEPTNO ENAME
------ ----------
??? 10 CLARK, KING, MILLER
??? 20 ADAMS, FORD, JONES, SCOTT, SMITH
??? 30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

?

例子如下：

SQL> create table idtable (id number,name varchar2(30));

Table created

SQL> insert into idtable values(10,'ab');

1 row inserted

SQL> insert into idtable values(10,'bc');

1 row inserted

SQL> insert into idtable values(10,'cd');

1 row inserted

SQL> insert into idtable values(20,'hi');

1 row inserted

SQL> insert into idtable values(20,'ij');

1 row inserted
SQL> insert into idtable values(20,'mn');

1 row inserted

SQL> select * from idtable;

?

??????? ID NAME
---------- ------------------------------
??????? 10 ab
??????? 10 bc
??????? 10 cd
??????? 20 hi
??????? 20 ij
??????? 20 mn

6 rows selected

SQL> select id,wmsys.wm_concat(name) name from idtable 2 group by id;

?

??????? ID NAME
---------- --------------------------------------------
??????? 10 ab,bc,cd
??????? 20 hi,ij,mn

?

SQL> select id,wmsys.wm_concat(name) over (order by id) name from idtable;

?

??????? ID NAME
---------- --------------------------------------------
??????? 10 ab,bc,cd
??????? 10 ab,bc,cd
??????? 10 ab,bc,cd
??????? 20 ab,bc,cd,hi,ij,mn
??????? 20 ab,bc,cd,hi,ij,mn
??????? 20 ab,bc,cd,hi,ij,mn

6 rows selected

?

SQL> select id,wmsys.wm_concat(name) over (order by id,name) name from idtable;

?

??????? ID NAME
---------- --------------------------------------------
??????? 10 ab
??????? 10 ab,bc
??????? 10 ab,bc,cd
??????? 20 ab,bc,cd,hi
??????? 20 ab,bc,cd,hi,ij
??????? 20 ab,bc,cd,hi,ij,mn

6 rows selected

个人觉得这个用法比较有趣.

?

SQL> select id,wmsys.wm_concat(name) over (partition by id) name from idtable;

?

??????? ID NAME
---------- --------------------------------------------
??????? 10 ab,bc,cd
??????? 10 ab,bc,cd
??????? 10 ab,bc,cd
??????? 20 hi,ij,mn
??????? 20 hi,ij,mn
??????? 20 hi,ij,mn

6 rows selected

?

SQL> select id,wmsys.wm_concat(name) over (partition by id,name) name from idtable;

?

??????? ID NAME
---------- --------------------------------------------
??????? 10 ab
??????? 10 bc
??????? 10 cd
??????? 20 hi
??????? 20 ij
??????? 20 mn

6 rows selected

?

ps:

wmsys.wm_concat、sys_connect_by_path、自定义行数实现行列转换：

CREATE TABLE tab_name(ID INTEGER NOT NULL PRIMARY KEY,cName VARCHAR2(20));
CREATE TABLE tab_name2(ID INTEGER NOT NULL,pName VARCHAR2(20));

INSERT INTO tab_name(ID,cName) VALUES (1,'百度');
INSERT INTO tab_name(ID,cName) VALUES (2,'Google');
INSERT INTO tab_name(ID,cName) VALUES (3,'网易');
INSERT INTO tab_name2(ID,pName) VALUES (1,'研发部');
INSERT INTO tab_name2(ID,pName) VALUES (1,'市场部');
INSERT INTO tab_name2(ID,pName) VALUES (