Geek Answers Handbook

Group_concat on Informix

You are group_concat for a query in Informix SQL that will mimic the MySQL group_concat function.

What is MySQL group_concat is the creation of an enumeration of all members in a group.

So, the data is 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

will create:

 items :price Paper,Pen:15 Sugar :15

What would be the most efficient way to achieve this in Informix? Should we use a stored procedure?

+2
source share
2 answers

To do this, you will need to define a user-defined aggregate. It has four parts - four functions (search for CREATE AGGREGATE in the IDS 12.10 Information Center :

  • Initializer (INIT)
  • Iterator (ITER)
  • Combine (COMBINE)
  • Finalizer (FINAL)

This is the official terminology in the capitals, and it is moderately intuitive. Think about calculating the average.

  • Initializer: set sum = 0; N = 0
  • Iterator: set sum + = x; N ++
  • Combinator: set sum = sum1 + sum2; set N = N1 + N2
  • Finalizer: result = sum / N - with N = 0 (with zero division) checks

The combiner is used to combine intermediate results from parallel execution; each parallel execution starts with an iterator and generates intermediate results. When parallel execution completes, individual value groups are combined with a combiner.

You can write similar code in IDS - using stored procedures or CDR or UDR.

See the SO question. Show a ratio from one to many as 2 columns - 1 unique row (identifier and semicolon) for the string function GROUP_CONCAT () is implemented in Informix.

+3
source

Informix has no built-in function for this. Does any other underlying DBMS have such a bizarre aggregate function? Ordering for a column not selected in the query is a bit dubious, but grouping? This is new to me.

You need to write a stored procedure or UDR to create such a dataset. To be honest, I would not try to do this in the database. This seems like the task most suitable for the consumer of this output (e.g. application / webapp / report -writer, etc.).

+1
source

More articles:


All Articles