sql有什么不可以的with t as (select 'a' name, '足球' hobby from dual union all select 'a' name, '篮球' hobby from dual union all select 'a' name, '乒乓' hobby from dual union all select 'b' name, '游泳' hobby from dual union all select 'b' name, '足球' hobby from dual union all select 'c' name, '篮球' hobby from dual) select t.name || '|' || listagg(hobby, ',') within group(order by t.name) from t group by t.name;
把上面的within group(order by t.name) 改成 within group(order by rownum) 就ok了。
SQL> select id||'|'||wm_concat(nn) from test1 group by id;ID||'|'||WM_CONCAT(NN) ---------------------------------------------------------------- a|足球,篮球,乒乓 b|游泳,足球 c|篮球
网上有很多类似的帖子。借用一下二楼数据: with t as ( select 'a' name, '足球' hobby from dual union all select 'a' name, '篮球' hobby from dual union all select 'a' name, '乒乓' hobby from dual union all select 'b' name, '游泳' hobby from dual union all select 'b' name, '足球' hobby from dual union all select 'c' name, '篮球' hobby from dual ) select t.name||'|'|| wmsys.wm_concat(hobby) from t group by t.name /* a|足球,篮球,乒乓 b|游泳,足球 c|篮球 */
然后把过程或者函数嵌在sql中
sql有什么不可以的with t as
(select 'a' name, '足球' hobby
from dual
union all
select 'a' name, '篮球' hobby
from dual
union all
select 'a' name, '乒乓' hobby
from dual
union all
select 'b' name, '游泳' hobby
from dual
union all
select 'b' name, '足球' hobby
from dual
union all
select 'c' name, '篮球' hobby from dual)
select t.name || '|' || listagg(hobby, ',') within group(order by t.name)
from t
group by t.name;
改成
within group(order by rownum)
就ok了。
----------------------------------------------------------------
a|足球,篮球,乒乓
b|游泳,足球
c|篮球
with t as (
select 'a' name, '足球' hobby from dual union all
select 'a' name, '篮球' hobby from dual union all
select 'a' name, '乒乓' hobby from dual union all
select 'b' name, '游泳' hobby from dual union all
select 'b' name, '足球' hobby from dual union all
select 'c' name, '篮球' hobby from dual )
select t.name||'|'|| wmsys.wm_concat(hobby)
from t
group by t.name
/*
a|足球,篮球,乒乓
b|游泳,足球
c|篮球
*/