根据第三列中的类型代码将列分为两列 [英] Split column into two columns based on type code in third column

问题描述

我的SQL非常生锈.我正在尝试转换此表:

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

+----+-----+--------------+-------+
| ID | SIN |   CONTACT    | TYPE  |
+----+-----+--------------+-------+
|  1 | 737 | [e-mail]  | email |
|  2 | 760 | 250-555-0100 | phone |
|  3 | 737 | 250-555-0101 | phone |
|  4 | 800 | 250-555-0102 | phone |
|  5 | 850 | [e-mail]  | email |
+----+-----+--------------+-------+

进入此表:

+----+-----+--------------+-------------+
| ID | SIN |    PHONE     |    EMAIL    |
+----+-----+--------------+-------------+
|  1 | 737 | 250-555-0101 | [e-mail] |
|  2 | 760 | 250-555-0100 |             |
|  4 | 800 | 250-555-0102 |             |
|  5 | 850 |              | [e-mail] |
+----+-----+--------------+-------------+

我写了这个查询:

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

哪个会产生:

+----+-----+--------------+-------+------+-------+-------------+--------+
| 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 | [e-mail] | email  |
|  4 | 800 | 250-555-0102 | phone |      |       |             |        |
|    |     |              |       |    5 |   850 | [e-mail] | email  |
+----+-----+--------------+-------+------+-------+-------------+--------+

我知道我可以选择所需的列，但SIN列不完整.我似乎记得我应该第三次加入表中以获取完整的SIN列，但我不记得是怎么回事.

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.

如何生成目标表(ID，SIN，PHONE，EMAIL)?

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

编辑和澄清:非常感谢您到目前为止所收到的答案，但是作为SQL角手，我不熟悉您使用的技术(条件语句，条件聚合和数据透视).使用JOIN和SELECT不能做到这一点吗?请原谅我对此事的无知. (这并不是说我对高级技术不感兴趣，但我不想太快地采取行动.)

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.)

推荐答案

一种解决此问题的方法是条件聚合:

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;

这篇关于根据第三列中的类型代码将列分为两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！