Informix – Merge Multiple Table Rows into One Row

informixmergerow

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

Best Answer

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.

Related Solutions

Can’t Select a View on Informix

You say the directory has rw permission; does it also have x permission. You need x permission to access the files in a directory (so you might set the permissions on the directory to ... gritted teeth ... 777, but you shouldn't. Is the directory owned by user informix and does it belong to group informix? Ideally, it should be owned by informix:informix, and the permissions should be 770 (or drwxrwx---).

Retrieve one of two possible values from 1 table in INFORMIX

This answer assumes that your table Ex1 has columns:

key_col — the key column.
flag_col — the flag column, containing L or P (with a check constraint).
data_col — a proxy for the unidentified set of columns containing the other data.
Primary key on key_col and flag_col.

A view like this should work (I didn't say it would work fast):

CREATE VIEW Relevant_Ex1(key_col, flag_col, data_col) AS
    SELECT A.Key_Col, A.Flag_Col, A.Data_Col
      FROM Ex1 AS A
      JOIN (SELECT B.Key_Col, MIN(Flag_Col) AS Flag_Col
              FROM Ex1 AS B
             GROUP BY B.Key_Col
           ) AS C
        ON A.Key_Col = C.Key_Col AND A.Flag_Col = C.Flag_Col;

This uses the fact that the flag column contains either L or P and L sorts before P. So, if there are two rows for a given key, the value L will be selected, else the value P will be selected.

Best Answer

Related Solutions

Can’t Select a View on Informix

Retrieve one of two possible values from 1 table in INFORMIX

Related Question