How to Transform One-to-Many Relationships into Distinct Columns in Informix SQL?

How to Display a One-to-Many Relationship as Distinct Columns

Problem:
You encounter a situation where data exhibits a one-to-many relationship, and you desire to represent it in a more structured and readable manner. Specifically, you want to transform a table with columns containing unique IDs and comma-separated lists of values into a table where each distinct value is displayed in a separate column.

Informix SQL Solution:
The recommended approach in Informix SQL is to utilize a user-defined aggregate function. While Informix does not natively offer a group_concat() function, you can create a custom aggregate that mimics its functionality.

Creating the User-Defined Aggregate:
The following code demonstrates how to create the necessary user-defined aggregate:

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);

Usage:
Once you have created the aggregate, you can leverage it in a query to achieve the desired transformation:

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

Example:
Let's consider an example table named anonymous_table:

CREATE TEMP TABLE anonymous_table
(
    id      INTEGER NOT NULL,
    codes   CHAR(4) NOT NULL,
    PRIMARY KEY (id, codes)
);

INSERT INTO anonymous_table VALUES(63592, 'PELL');
INSERT INTO anonymous_table VALUES(58640, 'SUBL');
INSERT INTO anonymous_table VALUES(58640, 'USBL');
INSERT INTO anonymous_table VALUES(73571, 'PELL');
INSERT INTO anonymous_table VALUES(73571, 'USBL');
INSERT INTO anonymous_table VALUES(73571, 'SUBL');

Applying the aforementioned query to this table would yield the following output:

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

This demonstrates how the aggregate function effectively groups and concatenates the codes associated with each unique ID, presenting the data in a more organized manner.

The above is the detailed content of How to Transform One-to-Many Relationships into Distinct Columns in Informix SQL?. For more information, please follow other related articles on the PHP Chinese website!