CODEWITHSUNDEEP
mysqlsqljoinself-join
Aakash Sahai
Aakash Sahai

Reputation: 4004

Merge two row results into one row MYSQL

I have a table having following structure:

| pid | email | email_type |
| 1   | x     | 1          |
| 1   | y     | 2          |
| 1   | z     | 3          |
| 2   | ab    | 1          |
| 3   | cd    | 2          |

Now I want my result for the pid parameter in format for email_type[1 & 2] only:

Case pid=1

| pid | email_p | email_w |
| 1   | x       | y       |

Case pid=2

| 2   | ab      | NULL    |

Case pid=3

| 3   | NULL    | cd      |

Here email_p represents email_type=1 & email_w represents email_type=2

I am using following query, and its working fine except for a case pid=2. Case I & Case III are successfully fetched. Please provide some solution with good explanation(if possible).

SELECT `e`.`pid`, `e`.`email` AS `email_p`, `e1`.`email` AS `email_w` FROM `table1` AS `e` LEFT JOIN `table1` AS `e1` ON e.pid=e1.pid AND e1.email_type=2 WHERE (e.pid IN (1) AND e.email_type=1)

It fails when e.pid = 2 and it returns empty result set & please provide solution e.pid IN (1,2,3) holds good for required format.#MYSQL

Upvotes: 0

Views: 97

Answers (1)

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

Try This

SELECT pid,
       MAX(CASE WHEN email_type=1 THEN email END ) as email_p ,
       MAX(CASE WHEN email_type=2 THEN email END ) as email_w 
FROM tableName
WHERE email_type IN (1,2)
GROUP BY pid

SQL Fiddle DEMO

Upvotes: 3

Related Questions