我有一个名为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));
编辑：由于海滩的答案修复上面
我更喜欢使用带有CASE表达式的GROUP BY解决方案.
SELECT
id,MAX(CASE WHEN emailRank = 1 THEN email END) AS [1],MAX(CASE WHEN emailRank = 2 THEN email END) AS [2],MAX(CASE WHEN emailRank = 3 THEN email END) AS [3],MAX(CASE WHEN emailRank = 4 THEN email END) AS [4]
FROM (
SELECT
id,ROW_NUMBER() OVER (PARTITION BY id ORDER BY email) AS emailRank
FROM TABLE
)
GROUP BY id;
原始Pivot示例具有类型并且缺少“)”.尝试以下方法以获得枢轴工作：
pivot( max(email) FOR emailRank IN (1,2,3));
