How Can I Transform a One-to-Many Relationship in Informix SQL into a Single Row with Comma-Separated Values?

Show a One-to-Many Relationship as Columns for Unique Rows (ID & Separated List)

This question addresses the issue of transforming data from a table with a one-to-many relationship into a table with each row containing a unique ID and a single column comprising comma-separated values representing the many values. The input data is structured as follows:

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

The objective is to transform this data into:

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

To achieve this transformation using Informix SQL, a user-defined aggregate (UDA) can be created:

CREATE AGGREGATE group_concat
    WITH (INIT = gc_init, ITER = gc_iter,
          COMBINE = gc_comb, FINAL = gc_fini);

Where the functions gc_init(), gc_iter(), gc_comb(), and gc_fini() are defined as follows:

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;

This UDA can then be used in a query to perform the data transformation:

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

The output of this query will be the desired transformed data.

The above is the detailed content of How Can I Transform a One-to-Many Relationship in Informix SQL into a Single Row with Comma-Separated Values?. For more information, please follow other related articles on the PHP Chinese website!