多列中的单列值

Question

ID|Class | Number
--+------+---------
1 | 1    |  58.2
2 | 1    |  85.4
3 | 2    |  28.2
4 | 2    |  55.4

期望的结果是：

Column1 |Number | Column2 | Number
--------+-------+---------+---------
1       | 58.2  | 2       |28.2
1       | 85.4  | 2       |55.4

需要什么SQL？

Answer 1

您可以使用row_number()并聚合：

select 1, max(case when seqnum % 2 = 1 then number end),
2, max(case when seqnum % 2 = 0 then number end)
from (select t.*,
row_number() over (partition by class order by id) as seqnum
from t
) t
group by ceiling(seqnum / 2.0);

聚合使用算术将每个类的成对行放在一行中。

Answer 2

尝试这个

SELECT 1 AS Column1,t2.Number,2 AS Column2,t1.Number
FROM
(
SELECT *
FROM test11
) t2
INNER JOIN
(
SELECT *
FROM test11
) t1
ON t1.Class = t2.Class
WHERE t1.ID < t2.ID
ORDER BY t1.ID DESC

db<中的演示>小提琴