informix-connstra

CREATE FUNCTION connstra_init (dummy lvarchar(4000))
   RETURNING lvarchar(4000);
   RETURN '';
END FUNCTION;
 
CREATE FUNCTION connstra_iter (result lvarchar(4000), value lvarchar(4000))
   RETURNING lvarchar(4000);
   RETURN result || value ||',';
END FUNCTION;
 
CREATE FUNCTION connstra_combine(partial1 lvarchar(4000), partial2 lvarchar(4000))
   RETURNING lvarchar(4000);
   RETURN partial1 || partial2;
END FUNCTION;
 
CREATE FUNCTION connstra_final(final lvarchar(4000))
   RETURNING lvarchar(4000);
   RETURN final;
END FUNCTION;
 
CREATE AGGREGATE connstra  WITH
   (INIT = connstra_init,
    ITER = connstra_iter,
    COMBINE = connstra_combine,
    FINAL = connstra_final);




--测试
create table t1(a int,b int);
insert into t1 values(1,2);
insert into t1 values(11,22);
insert into t1 values(111,222);
insert into t1 values(1111,333);


select * from t1;


> select connstra(a)  from t1;
connstra  1,11,111,1111, 


1 row(s) retrieved.


--去掉最后一个逗号
> select rtrim(connstra(a),',') from t1;


(expression)  1,11,111,1111 


1 row(s) retrieved.