views:

680

answers:

5

I need something similar to these 2 SO questions, but using Informix SQL syntax.

My data coming in looks like this:

id     codes

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

I want to see it come back like this:

id     codes 

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

See also group_concat() in Informix.

+1  A: 

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?

Jason Coyne
thats what i was planning to do, but i was just curious about doing it this way
CheeseConQueso
+1  A: 

I'd like to point you this answer on another similar question on Stack Overflow. You're looking for something like MySQL's group_concat() function.

Keith Gaughan
+1  A: 

Also, if informix allows you to create user-functions, you could create a function that returned a string with the concatenated value.

Jason Coyne
+4  A: 

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;
    RETURN partial1 || ',' || partial2;
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,USBL
63592 PELL
73571 PELL,SUBL,USBL
73572 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).

Jonathan Leffler
Had cause to use this in the real world, and found that the order of the concatenated values is not consistent. In other words it can't be used reliably to build a histogram of combinations. With a sufficiently large dataset, you will get a mixture of PELL,USBL,SUBL codes and PELL,SUBL,USBL codes.
RET
On reflection, this is a limitation in the COMBINE function. You would need to break the string back to an array, sort it and join it back together again. That's trivial in perl, but pretty dark magic in SPL...
RET
@RET: Thanks for the heads up. I agree that sorting in SPL is a non-trivial exercise (and that is being polite). And it is interesting to know that the ordering I saw was just an artefact of the data set. I did try a fair number of twists - but I'm not surprised to hear that the ordering was still coincidental.
Jonathan Leffler
A: 

I dont understand this statement

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

please help me to understand it

quanghai
post this as a new question... you'll get your answer, but you won't get it here
CheeseConQueso
@quanghai: Yes, please post this as a separate question. That way it will be posted on the main page of Stack Overflow and will a lot more people will see it.
Bill the Lizard