我的SQL很生锈。我正在尝试转换此表:
+----+-----+--------------+-------+ | 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 | +----+-----+--------------+-------+
进入此表:
+----+-----+--------------+-------------+ | 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 | +----+-----+--------------+-------------+
我写了这个查询:
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 | b@bacon.com | email | | 4 | 800 | 250-555-0102 | phone | | | | | | | | | | 5 | 850 | l@lemon.com | email | +----+-----+--------------+-------+------+-------+-------------+--------+
我知道我可以选择所需的列,但SIN列不完整。我似乎记得我应该第三次加入表以获得完整的SIN列,但是我不记得怎么做。
如何生成目标表(ID,SIN,PHONE,EMAIL)?
编辑和澄清:到目前为止,我非常感谢我收到的答案,但是作为SQL新手,我不熟悉您所使用的技术(条件语句,条件聚合和数据透视)。使用JOIN和SELECT不能做到这一点吗?请原谅我对此事的无知。(并不是我对高级技术不感兴趣,但我不想太快地采取行动。)
解决此问题的一种方法是条件聚合:
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;