Group Concatenation for Informix

While on other databases available by standard, Informix does not provide such a feature. But it can be implemented easily:


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 || '  <OR>  ' || TRIM(value);
    END IF;
END FUNCTION;

CREATE FUNCTION gc_combine(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 || '  <OR>  ' || partial2;
    END IF;
END FUNCTION;

CREATE FUNCTION gc_final(final LVARCHAR) RETURNING LVARCHAR;
    RETURN final;
END FUNCTION;

CREATE AGGREGATE group_concat
    WITH (INIT = gc_init, ITER = gc_iter,
          COMBINE = gc_combine, FINAL = gc_final);
		  
		  

Leave a Reply

Your email address will not be published. Required fields are marked *