group_concat in informix

Question

Am 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 Infomix? Would we definitely have to use a stored procedure?

Answer 1

There is certainly no built-in function in Informix to do this. Does any other mainstream RDBMS have such a bizarre aggregate function? Ordering by a column not selected in a query is a bit dubious, but grouping? That's a new one on me.

You would have to write a stored procedure or UDR to generate such a dataset. To be honest, I wouldn't attempt this in the database. It looks like a task best suited to the consumer of this output (ie the application/webapp/report-writer etc).

Answer 2

I understand that Informix does not have a built-in function that does this, am looking for a query that can simulate this behaviour. As of now I have a stored procedure that provides this information.

In fact, I do not find the function bizarre, AFAIK SQL standard does not mention that only mathematical aggregate functions should be applied on groups. I do not know of any other RDBMS that supports such a in-built function, though.

Answer 3

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 11.50 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 this 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.