Split rows into one row as comma delimited string

tyaramis · #1 (**permalink**) 11-27-09, 06:30

HI All

Is it possible to generate such a result set with a query in informix?

With my existing query I am getting this resultset:

SystemName Numbers
A 111
A 222
B 123
B 356
B 777
C 888

and I would like get a result set like this:

SystemName Numbers
A 111;222
B 123;356;777
C 888

Thanks alot in advance.

Tyveleyn · #2 (**permalink**) 11-27-09, 09:42

Not in a query. What you want to do should be done in a stored function or simply in a programming environment. For instance if you're working on a UNIX system and this query is executed in dbaccess you could redirect the Output To-pipe:

Code:

awk '{a[$1] = (length(a[$1]) > 0) ? a[$1] ";" $2 : a[$1] $2}END{for(i in a)print i, a[i]}'

Regards,
Hans

ceinma · #3 (**permalink**) 11-30-09, 12:04

using SPL (Stored Procedure) , yes you can.. but depending the amount of data , the performance will no be good.

If you need to much this and use IDS >= 9.40, can write a function in C, and probably your process speed will be much better.

Internet Services

Web Development

Digital Marketing

Consumer Tech