Home > Database > Mysql Tutorial > How to Transform a One-to-Many Relationship into Separate Columns in Informix SQL?

How to Transform a One-to-Many Relationship into Separate Columns in Informix SQL?

Barbara Streisand
Release: 2025-01-02 20:02:38
Original
1064 people have browsed it

How to Transform a One-to-Many Relationship into Separate Columns in Informix SQL?

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

Problem:

To transform data from a format like:

id     codes

63592  PELL
58640  SUBL
58640  USBL
73571  PELL
73571  USBL
73571  SUBL
Copy after login

into:

id     codes 

63592  PELL
58640  SUBL, USBL
73571  PELL, USBL, SUBL
Copy after login

Solution:

Utilizing a user-defined aggregate in Informix SQL can achieve this transformation.

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);
Copy after login

Example:

Applying this aggregate to the sample data with the following query:

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

Will produce the desired output:

58640 SUBL,USBL
63592 PELL
73571 PELL,SUBL,USBL
73572 PELL,SUBL,USBL
Copy after login

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

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template