1

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

ID  email
1   my_mail1@mail.com
2   my_mail2@mail.com
2   my_mail3@mail.com
2   my_mail4@mail.com
3   my_mail5@mail.com
4   my_mail6@mail.com

I want to create a query that would return this:

ID   email
1    my_mail1@mail.com
2    my_mail2@mail.com my_mail3@mail.com my_mail4@mail.com
3    my_mail5@mail.com
4    my_mail6@mail.com
1

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:

SELECT ID, GROUP_CONCAT(Email)
  FROM Anonymous_Table
 GROUP BY ID
 ORDER BY ID;

'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! – Ernesto Campohermoso Nov 3 '15 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. – Jonathan Leffler Nov 4 '15 at 0:00
1

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
from
 (
  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, privacy policy and cookie policy

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