SQL select - Group by and concatenate (no group_concat)

Question

I need some help with a problem on an IBM Informix 12.10. database.

CREATE TABLE toys(product VARCHAR(255), colour VARCHAR(255)); 
INSERT INTO toys (product, colour) 
VALUES ('balloon', 'red'), 
('balloon', 'white'),
('balloon', 'green'), 
('balloon', 'yellow'), 
('rubber duck', 'yellow'), 
('rubber duck', 'white');

I tried STUFF, GROUP_CONCAT and LISTAGG.

Initial table: "toys"

product     | colour 
-----------------------
balloon     | red
balloon     | green
balloon     | white  
balloon     | yellow  
rubber duck | yellow 
rubber duck | white

The resulting table should look like the this:

product     | colours 
-----------------------
balloon     | red, green, white, yellow
rubber duck | yellow, white

Thank you very much for your help!

Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Aug 01 '16 at 15:51
Uhm, it is an Informix database... I do not know more at the moment. — FutureCap, Aug 01 '16 at 15:58
Just a tip, you need to provide more detail than "not working". Include what you tried, error messages and codes, etc. Even seemingly insignificant details can provide info that can be used to get to the bottom of a problem. — Gary_W, Aug 01 '16 at 16:10
I've closed this as a duplicate of a question where there's code for the GROUP_CONCAT aggregate for Informix. If you want GROUP_CONCAT, use it. — Jonathan Leffler, Aug 01 '16 at 22:17

Gary_W · Answer 1 · 2016-08-01T16:10:51.287

0

In Oracle the listagg() function does the trick. Hopefully Informix has a similar function:

SQL> column PRODUCT format A20
SQL> column COLOURS format A40
SQL> with tbl(product, colour) as (
     select 'balloon', 'red' from dual union
     select 'balloon', 'green' from dual union
     select 'balloon', 'white' from dual union
     select 'balloon', 'yellow' from dual union
     select 'rubber duck', 'yellow' from dual union
     select 'rubber duck', 'white' from dual
   )
   select product, listagg(colour, ', ') within group (order by colour) as colours
   from tbl
   group by product;

PRODUCT              COLOURS
-------------------- ----------------------------------------
balloon              green, red, white, yellow
rubber duck          white, yellow

SQL>

edited Aug 01 '16 at 16:10

answered Aug 01 '16 at 16:00

Gary_W

9,933
1
22
40

user lacks privilege or object not found: LISTAGG :-( – FutureCap Aug 01 '16 at 16:02
Try this maybe? https://github.com/jOOQ/jOOQ/issues/1276. You are going to have to do some searching on your own too to try to emulate this with the functionality Informix provides. Remember to post back the answer once you find it for future searchers! Good luck! – Gary_W Aug 01 '16 at 16:07
Hmm. "user lacks privilege or object not found: XMLSERIALIZE" `select product, substr( xmlserialize( xmlagg( xmltext( concat( ', ', colour ) ) ) as varchar( 1024 ) ), 3 ) from toys group by product` The substr looks strange in my opinion. – FutureCap Aug 01 '16 at 16:16

SQL select - Group by and concatenate (no group_concat)

1 Answers1