Show a one to many relationship as 2 columns - 1 unique row (ID & comma separated list)

I believe that the answer you need is a user-defined aggregate, similar to this one:

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

Given a table of elements (called elements) with a column called name containing (funnily enough) the element name, and another column called atomic_number, this query produces this result:

SELECT group_concat(name) FROM elements WHERE atomic_number < 10;Hydrogen,Helium,Lithium,Beryllium,Boron,Carbon,Nitrogen,Oxygen,Fluorine

Applied to the question, you should obtain the answer you need from:

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

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');INSERT INTO anonymous_table VALUES(73572, 'USBL');INSERT INTO anonymous_table VALUES(73572, 'PELL');INSERT INTO anonymous_table VALUES(73572, 'SUBL');SELECT id, group_concat(codes)    FROM anonymous_table    GROUP BY id    ORDER BY id;

The output from that is:

58640 SUBL,USBL63592 PELL73571 PELL,SUBL,USBL73572 PELL,SUBL,USBL

The extra set of data was added to test whether insert sequence affected the result; it appears not to do so (the codes are in sorted order; I'm not sure whether there's a way to alter - reverse - that order).

Notes:

1. This aggregate should be usable for any type that can be converted to VARCHAR(255), which means any numeric or temporal type. Long CHAR columns and blob types (BYTE, TEXT, BLOB, CLOB) are not handled.
2. The plain LVARCHAR limits the aggregate size to 2048 bytes. If you think you need longer lengths, specify LVARCHAR(10240) (for 10 KiB), for example.
3. As of Informix 12.10.FC5, the maximum length that works seems to be 16380; anything longer seems to trigger SQL -528: Maximum output rowsize (32767) exceeded, which surprises me.

4. If you need to remove the aggregate, you can use:

   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;

Oracle provides list aggregator function for such requirement.

SELECT id, LISTAGG(codes,',') as CODE_LIST FROM <TABLE> GROUP BY id

Output will be like

ID     CODE_LIST 63592  PELL58640  SUBL,USBL73571  PELL,USBL,SUBL

I'm not sure about informix sql, but in MSSQL or Oracle, you could do this with the

DECODE or CASE keywords, by concatenating them together. However, this would require you to know all the potential values ahead of time, which is brittle.

I'm assuming the reason you don't like the STUFF keyword is because informix does not support it?

Oracle also supports the CONNECT BY keywords, which would work, but again may not be supported by informix.

Probably the best answer would be to build this output in your client/data layer, after the query. Is there a particular reason why this must be done in the query?