idam oracle_oracle中的wm_concat对应达梦的是什么？

本帖最后由 thunder 于 2012-10-17 08:00 AM 编辑

在达梦中，实现类似于oracle的wmsys.wm_concat，完成结果集行列转换，在此总结一下，几种方法：

create table idtable (id int,name varchar(30));

insert into idtable values(10,'ab');

insert into idtable values(10,'bc');

insert into idtable values(10,'cd');

insert into idtable values(20,'hi');

insert into idtable values(20,'ij');

insert into idtable values(20,'mn');

转换前：

ID        NAME

10        ab

10        bc

10        cd

20        hi

20        ij

20        mn

转换后：

ID        NAME

10        ab,bc,cd

20        hi,ij,mn

方法一:--层次查询sys_connect_by_path

select

id,

ltrim(max(sys_connect_by_path(name, ',')), ',')

from

(

select

row_number() over(PARTITION by id ORDER by name) r,

id,

name

from

idtable

)

start

with

r=1 CONNECT by prior r =r-1

and prior id = id

group by

id

order by

id;

方法二：--case..when..then情况表达式，但是按id分组，每组中个数不宜太多

select

id,

max(case when r=1 then name end)+','+

max(case when r=2 then name end)+','+

max(case when r=3 then name end) name

from (

select

row_number() over(PARTITION by id ORDER by name) r,

id,

name

from

idtable )

group by id;

方法三：--decode的函数的使用,但是按id分组，每组中个数不宜太多

select

id,

max(decode(r,1,name))+','+

max(decode(r,2,name))+','+

max(decode(r,3,name)) name

from (

select

row_number() over(PARTITION by id ORDER by name) r,

id,

name

from

idtable )

group by

id;

方法四：--自定义函数(隐式游标)

create or replace function chtorow(chid int)return varchar(1000)

is

declare

result varchar(1000):='';

begin

for cur in (select name from idtable where id=chid) loop

result:=result||','||cur.name;

end loop;

return ltrim(result,',');

end;

select id,chtorow(id) name from idtable group by id;