Implementing mysql group_concat() aggregate in Informix IDS 11.5 - thanks to Jonathan Leffler.
As DBA do:
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;
RETURN partial1 || ',' || partial2;
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);
Then you need to grant access to the functions to users that need it:
grant execute on function gc_init to appsql;
grant execute on function gc_iter to appsql;
grant execute on function gc_comb to appsql;
grant execute on function gc_fini to appsql;
Example Use:
select ord_id, group_concat(product)
from ord
group by ord_id
