SQL select - Group by and concatenate (no group_concat) -

this question has answer here:

• show 1 many relationship 2 columns - 1 unique row (id & comma separated list) 5 answers

i need problem on ibm informix 12.10. database.

create table toys(product varchar(255), colour varchar(255));  insert toys (product, colour)  values ('balloon', 'red'),  ('balloon', 'white'), ('balloon', 'green'),  ('balloon', 'yellow'),  ('rubber duck', 'yellow'),  ('rubber duck', 'white');  

i tried stuff, group_concat , listagg.

initial table: "toys"  product     | colour  ----------------------- balloon     | red balloon     | green balloon     | white   balloon     | yellow   rubber duck | yellow  rubber duck | white  

the resulting table should this:

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

thank help!

in oracle listagg() function trick. informix has similar function:

sql> column product format a20 sql> column colours format a40 sql> tbl(product, colour) (      select 'balloon', 'red' dual union      select 'balloon', 'green' dual union      select 'balloon', 'white' dual union      select 'balloon', 'yellow' dual union      select 'rubber duck', 'yellow' dual union      select 'rubber duck', 'white' dual    )    select product, listagg(colour, ', ') within group (order colour) colours    tbl    group product;  product              colours -------------------- ---------------------------------------- balloon              green, red, white, yellow rubber duck          white, yellow  sql>