自定义 group_concat

CREATE FUNCTION gc_init(dummy VARCHAR(255)) RETURNING LVARCHAR;

RETURN '';

END FUNCTION;

CREATE FUNCTION gc_iter(result LVARCHAR, value VARCHAR(255))

RETURNING LVARCHAR;

IF result = '' THEN

RETURN TRIM(value);

ELSE

RETURN result || ',' || TRIM(value);

END IF;

END FUNCTION;

CREATE FUNCTION gc_comb(partial1 LVARCHAR, partial2 LVARCHAR)

RETURNING LVARCHAR;

IF partial1 IS NULL OR partial1 = '' THEN

RETURN partial2;

ELIF partial2 IS NULL OR partial2 = '' THEN

RETURN partial1;

ELSE

RETURN partial1 || ',' || partial2;

END IF;

END FUNCTION;

CREATE FUNCTION gc_fini(final LVARCHAR) RETURNING LVARCHAR;

RETURN final;

END FUNCTION;

CREATE AGGREGATE group_concat

WITH (INIT = gc_init, ITER = gc_iter,

COMBINE = gc_comb, FINAL = gc_fini);

----测试

举例：

create table test_str(t1 integer,t2 varchar(255));

表test_str的数据如下

insert into test_str values (1,'A');

insert into test_str values (1,'B');

insert into test_str values (1,'C');

insert into test_str values (2,'A');

insert into test_str values (2,'B');

insert into test_str values (3,'A');

insert into test_str values (4,'A');

select t1,group_concat(t2) t2_sum from test_str

where 1=1

group by t1 order by t1;