Oracle10g SQL pivot

前端之家收集整理的这篇文章主要介绍了Oracle10g SQL pivot前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有一个名为TABLE的表,例如:
  1. ID | email
  2. --------------
  3. 1 | a@a.com
  4. 1 | b@b.com
  5. 2 | c@c.com
  6. 3 | d@d.com
  7. 3 | e@e.com

我想回复一些类似的东西

  1. ID | email1 | email2
  2. --------------------
  3. 1 | a@a.com| b@b.com
  4. 2 | c@c.com|
  5. 3 | d@d.com| e@e.com

我想知道如何使用透视来帮助我摆脱重复的ID行,并为其他电子邮件添加额外的列.谢谢您的帮助.

  1. SELECT id,email1,email2,email3
  2. FROM (
  3. SELECT id,email,ROW_NUMBER() OVER (PARTITION BY id ORDER BY email) AS emailRank
  4. FROM TABLE
  5. )
  6. pivot( max(email) FOR emailRank IN (1 as email1,2 as email2,3 as email3));

编辑:由于海滩的答案修复上面

我更喜欢使用带有CASE表达式的GROUP BY解决方案.
  1. SELECT
  2. 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]
  3. FROM (
  4. SELECT
  5. id,ROW_NUMBER() OVER (PARTITION BY id ORDER BY email) AS emailRank
  6. FROM TABLE
  7. )
  8. GROUP BY id;

原始Pivot示例具有类型并且缺少“)”.尝试以下方法以获得枢轴工作:

  1. pivot( max(email) FOR emailRank IN (1,2,3));

猜你在找的Oracle相关文章