Oracle10g SQL pivot(Oracle10g SQL 枢轴)
问题描述
我有一个名为 TABLE 的表,例如:
I have a table named TABLE for example looking like:
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 行,并为他们的其他电子邮件添加一个额外的列.感谢您的帮助.
I was wondering how I could use pivoting to help me get rid of duplicate ID rows and just add an extra column for their other emails. Thanks for the help.
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));
由于海滩的回答,上面已修复
fixed above thanks to beach's answer
推荐答案
我更喜欢使用带有 CASE 表达式的 GROUP BY 解决方案.
I prefer using the GROUP BY solution with CASE expression.
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,
email,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY email) AS emailRank
FROM TABLE
)
GROUP BY id;
原始 Pivot 示例具有类型且缺少)".尝试以下操作以使枢轴工作:
Original Pivot example had type and missing ")". Try the following to get pivot working:
pivot( max(email) FOR emailRank IN (1,2,3));
这篇关于Oracle10g SQL 枢轴的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:Oracle10g SQL 枢轴


- 在SQL中,如何为每个组选择前2行 2021-01-01
- 如何将 SonarQube 6.7 从 MySQL 迁移到 postgresql 2022-01-01
- 使用 Oracle PL/SQL developer 生成测试数据 2021-01-01
- 如何使用 pip 安装 Python MySQLdb 模块? 2021-01-01
- 如何将 Byte[] 插入 SQL Server VARBINARY 列 2021-01-01
- 以一个值为轴心,但将一行上的数据按另一行分组? 2022-01-01
- 导入具有可变标题的 Excel 文件 2021-01-01
- 更改自动增量起始编号? 2021-01-01
- 远程 mySQL 连接抛出“无法使用旧的不安全身份验证连接到 MySQL 4.1+"来自 XAMPP 的错误 2022-01-01
- SQL 临时表问题 2022-01-01