How to Display One-to-Many Relationships as Comma-Separated Lists in Informix SQL?

One-to-Many Relationship as Unique Rows with Comma-Separated Lists Using Informix SQL

Similar to questions posed on Stack Overflow, this article provides an Informix SQL solution to display a one-to-many relationship as a single unique row with comma-separated lists.

Initial Data

id     codes
63592  PELL
58640  SUBL
58640  USBL
73571  PELL
73571  USBL
73571  SUBL

Desired Output

id     codes
63592  PELL
58640  SUBL, USBL
73571  PELL, USBL, SUBL

Custom User-Defined Aggregate

To achieve the desired output, a custom user-defined aggregate (UDA) is required. Below is an example UDA named 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);

Query with Grouped Concatenation

Applying the group_concat UDA to the original data, we can group by the unique id and aggregate the codes using the custom UDA:

SELECT id, group_concat(codes)
    FROM anonymous_table
    GROUP BY id;

Output

58640 SUBL,USBL
63592 PELL
73571 PELL,SUBL,USBL

Additional Notes

• This UDA handles data types that can be converted to VARCHAR(255) (e.g., numeric or temporal types).
• As of Informix 12.10.FC5, the maximum length of the aggregate result appears to be 16380 bytes.
• To remove the UDA and associated functions, execute the following commands:

DROP AGGREGATE IF EXISTS group_concat;
DROP FUNCTION IF EXISTS gc_fini;
DROP FUNCTION IF EXISTS gc_init;
DROP FUNCTION IF EXISTS gc_iter;
DROP FUNCTION IF EXISTS gc_comb;

The above is the detailed content of How to Display One-to-Many Relationships as Comma-Separated Lists in Informix SQL?. For more information, please follow other related articles on the PHP Chinese website!