SQL 列转行的对比

MYSQL：
SELECT id,group_concat(name) FROM table group by id;
SELECT id,group_concat(distinct name order by lev desc) FROM table group by id;

ORACLE:
vm_group

SELECT id,wm_group(name) from table group by id;

for example:
select id,val from idtable;
ID VAL
---------- --------------------
10 abc
10 abc
10 def
10 def
20 ghi
20 jkl
20 mno
20 mno
8 rows selected
SQL> commit;
SQL> SELECT ID,WMSYS.WM_CONCAT(VAL)AS ENAMES
2 FROM IDTABLE
3 GROUP BY ID;

ID ENAMES
---------- --------------------------------------------------------------------------------
10 abc,abc,def,def
20 ghi,jkl,mno,mno

******************************************************************************************************

listagg

Base Data:

DEPTNO ENAME
---------- ----------
20 SMITH
30 ALLEN
30 WARD
20 JONES
30 MARTIN
30 BLAKE
10 CLARK
20 SCOTT
10 KING
30 TURNER
20 ADAMS
30 JAMES
20 FORD
10 MILLER
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
Desired Output:

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