insert into test1 values(1,'a');
insert into test1 values(1,'b');
insert into test1 values(1,'c');
insert into test1 values(2,'d');
insert into test1 values(2,'e');
效果1:行转列,默认逗号分隔 select wm_concat(name) name from test1;
NAME
a,b,c,d,e
效果2:把结果里的逗号替换成"|"
select replace(wm_concat(name),',','|') from test1;
NAME
a|b|c|d|e
效果3:按ID分组合并name
select id,wm_concat(name) name from test1 group by id;
ID
NAME
1
a,b,c
2
d,e
懒人用法扩展: 案例:我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat来让这个需求变简单,假设我的APP_USER表中有(id,username,password,age)4个字段。查询结果如下
select 'create or replace view as select '|| wm_concat(column_name) || ' from APP_USER'sqlStr fromuser_tab_columns wheretable_name='APP_USER'; create or replace view as select id,username,password,age from APP_USER