Search code examples
sqlinformix

group_concat in Informix

Looking for a query in Informix's SQL that will simulate MySQL's group_concat function.

What MySQL's group_concat does is it creates an enumeration of all members in the group.

So with the data as follows:

orderid itemName price
1 Paper 10
1 Pen 5
2 Sugar 15

and the following query:

select group_concat(itemName), sum(price)
from order_details
group by orderid

would produce:

items price
Paper,Pen 15
Sugar 15

What would be most efficient way to achieve this in Informix? Would we definitely have to use a stored procedure?

Solution

  • You would have to define a user-defined aggregate to do this. That has four parts - four functions (search for CREATE AGGREGATE in the IDS 12.10 Info Centre):

    1. Initializer (INIT)
    2. Iterator (ITER)
    3. Combine (COMBINE)
    4. Finalizer (FINAL)

    That's the official terminology in capitals, and it is moderately intuitive. Think of calculating an average.

    1. Initializer: set sum = 0; N = 0
    2. Iterator: set sum += x; N++
    3. Combiner: set sum = sum1 + sum2; set N = N1 + N2
    4. Finalizer: result = sum / N -- with N=0 (zero-divide) checks

    The combiner is used to combine intermediate results from parallel execution; each parallel execution starts with the iterator and generates intermediate results. When the parallel execution completes, the separate sets of values are combined with the combiner.

    You can write analogous code in IDS - using stored procedures or C or Java UDRs.

    See the SO question Show a one to many relationship as 2 columns — 1 unique row (ID & comma separated list) for a string-based GROUP_CONCAT() function implemented in Informix.