WITH a AS( SELECT '001' col1,'a' col2 FROM dual UNION ALL SELECT '001' col1,'b' col2 FROM dual) select col1,replace(wm_concat(col2),',','') from a group by col1;
不止两条记录,oracle也支持 这种语法????
with tmp as ( select '001' col1, 'a' col2 from dual union all select '001' col1, 'b' col2 from dual ) select col1, replace(wm_concat(col2),',','') col2 from tmp group by col1;COL1 COL2 ---- ---- 001 ab
对的 oracle10g以上版本有wm_concat()这个函数,这个函数可以进行字段拼接
--table---- create table test( name varcahr2(10), type varchar2(2))---------sql---------- select name,wmsys_wm_concat(type) types from test group by name
1楼只是给你举个例子,你直接用 select col1,replace(wm_concat(col2),',','') from a group by col1; 就可以了
select col1,lead(col2,2)over(order by col1)col2 from a
SELECT '001' col1,'a' col2 FROM dual
UNION ALL
SELECT '001' col1,'b' col2 FROM dual)
select col1,replace(wm_concat(col2),',','') from a group by col1;
(
select '001' col1, 'a' col2 from dual
union all
select '001' col1, 'b' col2 from dual
)
select col1, replace(wm_concat(col2),',','') col2 from tmp
group by col1;COL1 COL2
---- ----
001 ab
--table----
create table test(
name varcahr2(10),
type varchar2(2))---------sql----------
select name,wmsys_wm_concat(type) types
from test group by name
1楼只是给你举个例子,你直接用
select col1,replace(wm_concat(col2),',','') from a group by col1;
就可以了