Split column into two columns based on type code in third column

Question

My SQL is very rusty. I'm trying to transform this table:

+----+-----+--------------+-------+
| ID | SIN |   CONTACT    | TYPE  |
+----+-----+--------------+-------+
|  1 | 737 | b@bacon.com  | email |
|  2 | 760 | 250-555-0100 | phone |
|  3 | 737 | 250-555-0101 | phone |
|  4 | 800 | 250-555-0102 | phone |
|  5 | 850 | l@lemon.com  | email |
+----+-----+--------------+-------+

Into this table:

+----+-----+--------------+-------------+
| ID | SIN |    PHONE     |    EMAIL    |
+----+-----+--------------+-------------+
|  1 | 737 | 250-555-0101 | b@bacon.com |
|  2 | 760 | 250-555-0100 |             |
|  4 | 800 | 250-555-0102 |             |
|  5 | 850 |              | l@lemon.com |
+----+-----+--------------+-------------+

I wrote this query:

SELECT *
  FROM (SELECT *
          FROM people
         WHERE TYPE = 'phone') phoneNumbers
       FULL JOIN (SELECT *
                    FROM people
                   WHERE TYPE = 'email') emailAddresses
          ON phoneNumbers.SIN = emailAddresses.SIN;

Which produces:

+----+-----+--------------+-------+------+-------+-------------+--------+
| ID | SIN |   CONTACT    | TYPE  | ID_1 | SIN_1 |  CONTACT_1  | TYPE_1 |
+----+-----+--------------+-------+------+-------+-------------+--------+
|  2 | 760 | 250-555-0100 | phone |      |       |             |        |
|  3 | 737 | 250-555-0101 | phone |    1 |   737 | b@bacon.com | email  |
|  4 | 800 | 250-555-0102 | phone |      |       |             |        |
|    |     |              |       |    5 |   850 | l@lemon.com | email  |
+----+-----+--------------+-------+------+-------+-------------+--------+

I know that I can select the columns I want, but the SIN column is incomplete. I seem to recall that I should join in the table a third time to get a complete SIN column, but I cannot remember how.

How can I produce my target table (ID, SIN, PHONE, EMAIL)?

Edit and clarification: I am grateful for the answers I have received so far, but as a SQL greenhorn I am unfamiliar with the techniques you are using (case statements, conditional aggregation, and pivoting). Can this not be done using JOIN and SELECT? Please excuse my ignorance in this matter. (It's not that I am not interested in superior techniques, but I do not want to move too fast too soon.)

I messed around with this for a while, and the only other thing that could work for this would be multiple UNIONs, and that might not even work right for this scenario. Your easiest option is using CASE, which is just an IF loop for SQL. — The_DemoCorgin, Jun 17 '14 at 21:06

score 3 · Accepted Answer · answered Jun 17 '14 at 18:38

3

One way to approach this is conditional aggregation:

select min(ID), SIN, 
       max(case when type = 'phone' then contact end) as phone,
       max(case when type = 'email' then contact end) as email
from people t
group by sin;

answered Jun 17 '14 at 18:38

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Jun 17 '14 at 18:45

1

Seems a pivot (oracle.com) would work easily here.

SELECT ID, SIN, PHONE, EMAIL
FROM PEOPLE
PIVOT (
    MAX(CONTACT)
    FOR TYPE IN ('EMAIL', 'PHONE')
)

answered Jun 17 '14 at 18:45

Jaaz Cole

3,119
1
15
20

score 0 · Answer 3 · answered Jun 17 '14 at 23:01

I realize this is less elegant than all the solutions posted, but here it is anyhow, a solution using only JOIN and SELECT:

SELECT sins.SIN, phone, email
  FROM ((SELECT SIN email_sin, contact email
           FROM people
          WHERE TYPE = 'email') email
        FULL JOIN (SELECT SIN phone_sin, contact phone
                     FROM people
                    WHERE TYPE = 'phone') phone
           ON email.email_sin = phone.phone_sin)
       RIGHT JOIN (SELECT DISTINCT SIN FROM people) sins
          ON sins.SIN = phone_sin OR sins.SIN = email_sin;

This lacks the ID column.

Split column into two columns based on type code in third column

3 Answers3

Linked