I have problem just like this one but in Informix. I have table with 2 attributes ID, email like this one:

ID  email
1   [email protected]
2   [email protected]
2   [email protected]
2   [email protected]
3   [email protected]
4   [email protected]

I want to create a query that would return this:

ID   email
1    [email protected]
2    [email protected] [email protected] [email protected]
3    [email protected]
4    [email protected]

2 Answers 2


There is an answer on Stack Overflow to the question Show a one to many relationship as 2 columns — 1 unique row (ID & comma separated list) which demonstrates how to create a user-defined aggregate in Informix that functions more or less like the GROUP_CONCAT aggregate in MySQL.

You can then easily use this to solve the problem in this question:

  FROM Anonymous_Table

'Tis curious that both this question and the cross-referenced one have anonymous tables — it is one of the common flaws in questions about databases on the Stack Exchange family of sites.

  • Doesn't work on INFORMIX! Nov 3, 2015 at 23:57
  • @ErnestoCampohermoso: what do you mean "Doesn't work on Informix"? If your version of Informix is so old that the name is written as INFORMIX, then you're right. However, if you install the necessary code for GROUP_CONCAT (as shown in the other question on SO), then it works — or, at least it works for me on the systems I have tested it on. You do have to install the software; I said you have to create the user-defined aggregate. If you've not done that, it won't work, of couse. Nov 4, 2015 at 0:00

If you have version 12.1 the following method also works, and is especially useful if you don't have access rights to create procedures on the production database.

It uses rank()over() to group the email addresses by id and sequence them, and then sys_connect_by_path() to do the concatenation.

select id,  sys_connect_by_path(trim(email),' ') as email
  select id, email,
         rank() over (partition by id order by email) as seq,
         rank() over (partition by id order by email desc) as rseq
    from emails
 where rseq = 1 start with seq = 1
 connect by prior id=id and prior seq=seq-1
 order by id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.