多行 合并为一行

[wang7890]

表A 如下所示：

id name
1 zhang
2 wang
3 zhao
4 sun

我想合并为一行，如下所示:

1,2,3,4 zhang, wang,zhao,sun

怎么写这个语句？ 请大家赐教！谢谢！

[gisinfo]

select wmsys.wm_concat(id),wmsys.wm_concat(name) from a


在10G以上版本

[guojh021]

来自:oracledbalgtu
给你个参考方法:
SELECT DEPTNO, MAX(ONCATENATED)
FROM (SELECT DEPTNO,
LTRIM(SYS_CONNECT_BY_PATH(ENAME, ','), ',') ONCATENATED
FROM (SELECT DEPTNO,
ENAME,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY ENAME) AS CURR,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY ENAME) - 1 AS PREV
FROM SCOTT.EMP)
START WITH CURR = 1
CONNECT BY PREV = PRIOR CURR
AND DEPTNO = PRIOR DEPTNO)
GROUP BY DEPTNO
ORDER BY 1;

[范佩西_11]

select wmsys.wm_concat(id),wmsys.wm_concat(name) from a

[willflyz]

楼上的方法比较独特,不过好象不太对哦.
因为没有环境自己没办法验证,呵呵..

[guojh021]

[Quote=引用 2 楼 linzhangs 的回复:]
SQL code
select wmsys.wm_concat(id),wmsys.wm_concat(name) from a


[/Quote]

这个很好.

[hebo2005]

[Quote=引用 2 楼 linzhangs 的回复:]
SQL codeselect wmsys.wm_concat(id),wmsys.wm_concat(name) from a
[/Quote]
10G的用法

[sleepzzzzz]

select wmsys.wm_concat(id)||' '||wmsys.wm_concat(name) from a

[yonglinc]

学习