Informix - Aggregating list of values into comma-delimited string

Question

Is there a way to convert a list of values into a comma-delimited string in Informix? For example, I have the following query as a subselect:

SELECT [State] From States

I would like to convert all the values from that select into a comma-separated list.

Can I do that in informix?

You can do it but be careful though or Informix will corrupt your data. — ChaosPandion, Sep 24 '10 at 15:40
I am outputting the values to a report. I am not inserting it anywhere. In any case, how can I do it? Is there a built in function that I can utilize? — Skadoosh, Sep 24 '10 at 15:43
Possible duplicate of [Show a one to many relationship as 2 columns - 1 unique row (ID & comma separated list)](https://stackoverflow.com/questions/715350/show-a-one-to-many-relationship-as-2-columns-1-unique-row-id-comma-separate) — alk, Aug 10 '18 at 10:18

score 3 · Accepted Answer · edited May 23 '17 at 12:26

3

I think the answer you need is given in these questions: SO 715350, SO 489081. It shows how to create and use a GROUP_CONCAT() aggregate that will do exactly what you want. The functionality is otherwise not available - that is, you have to add it to Informix, but it can (fairly) easily be added.

edited May 23 '17 at 12:26

Community

1
1

answered Sep 24 '10 at 18:32

Jonathan Leffler

730,956
141
904
1,278

score 0 · Answer 2 · answered Feb 28 '23 at 13:34

You can always use recursive SQL:

with 
  states (rn, state) as (
    select row_number() over (), s 
    from table (multiset {'a', 'b', 'c'}) as t (s)
  ),
  r (rn, s) as (
    select s.rn, cast(s.state as varchar(100))
    from states as s
    where s.rn = 1
    union all
    select s.rn, cast(r.s || ', ' || s.state as varchar(100))
    from states as s
    join r on s.rn = r.rn + 1
  )
select first 1 s 
from r
order by rn desc;

Producing:

|s      |
|-------|
|a, b, c|

Informix - Aggregating list of values into comma-delimited string

2 Answers2