我有一个名为TABLE的表,例如:
ID | email
--------------
1 | a@a.com
1 | b@b.com
2 | c@c.com
3 | d@d.com
3 | e@e.com
我想回复一些类似的东西
ID | email1 | email2
--------------------
1 | a@a.com| b@b.com
2 | c@c.com|
3 | d@d.com| e@e.com
我想知道如何使用透视来帮助我摆脱重复的ID行,并为其他电子邮件添加额外的列.谢谢您的帮助.
SELECT id, email1, email2, email3
FROM (
SELECT id,
email,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY email) AS emailRank
FROM TABLE
)
pivot( max(email) FOR emailRank IN (1 as email1, 2 as email2, 3 as email3));
编辑:由于海滩的答案修复上面