下面是原始表格
+----------+---------+
| Trade | Key |
+----------+---------+
| A | 1 |
| A | 2 |
| A | 3 |
| B | 1 |
| B | 2 |
| B | 3 |
+----------+---------+
以下是我需要的结果
+----------+---------+---------+---------+
| Trade | Key1 | Key2 | Key3 |
+----------+---------+---------+---------+
| A | 1 | 2 | 3 |
| B | 1 | 2 | 3 |
+----------+---------+---------+---------+
欢迎使用指向SQL代码的任何指针。
提前感谢
Sarge
查找PIVOT。正是sql函数使您能够准确地执行所需操作。
PIVOT的缺点是并非所有数据库都支持它。请确保您的数据库支持它。
查看以下答案以获得进一步解释:https://stackoverflow.com/a/15931734/16462128
我想你想要这样的条件聚合:
select trade,
max(case when seqnum = 1 then key end) as key_1,
max(case when seqnum = 2 then key end) as key_2,
max(case when seqnum = 3 then key end) as key_3
from (select t.*,
row_number() over (partition by trade order by key) as seqnum
from t
) t
group by trade;
您需要明确列出列的数量,以确保您获得所有交易的所有密钥(您的示例数据有三个(。