所有版本的oracle都可以使用wm_concat()函数 例:select wm_concat(name) as name from user;
但如果是oracle11g,使用listagg() within group()函数 例:select listagg(name, ',' ) within group( order by name) as name from user;
例:
CREATE TABLE MyTest(xType NUMBER ,City nvarchar2(200 ));
/
INSERT INTO MyTest(xType,City)
SELECT 1 ,'北京' FROM dual UNION ALL
SELECT 1 ,'上海' FROM dual UNION ALL
SELECT 1 ,'广州' FROM dual UNION ALL
SELECT 2 ,'武汉' FROM dual UNION ALL
SELECT 2 ,'杭州' FROM dual UNION ALL
SELECT 2 ,'厦门' FROM dual
COMMIT ;
使用wm_Concat:
SELECT xType,wmsys.wm_concat(to_char(City)) AS xCity FROM MyTest GROUP BY xType
使用ListAgg:
SELECT xType, ListAgg(to_char(City),',' ) WITHIN GROUP (ORDER BY xType) AS xCity FROM MyTest GROUP BY xType
结果:
XTYPE XCITY
1 北京,广州,上海
2 杭州,武汉,厦门
http://blog.csdn.net/u014267869/article/details/52195753