问题描述
我有一张看起来像的桌子
I have a table that looks like
Test Reader Result
1 John 1.6
1 Jack 5.2
2 Ursula 2.5
3 Albert 3.0
2 Jack 5.1
而且我知道每个测试最多可以有 3 个 Readers 所以我想创建一个看起来像的表格
And I know each test can have a maximum of 3 Readers so I want to create a table that looks like
Test Reader_1 Result_1 Reader_2 Result_2 Reader_3 Result_3
1 John 1.6 Jack 5.2 (null) (null)
2 Ursula 2.5 Jack 5.1 (null) (null)
3 Albert 3.0 (null) (null) (null) (null)
我环顾四周，似乎需要使用 PIVOT 表.唯一的问题是我找到了使用序数变量创建列的示例.在这种情况下，我有预定义数量的可能列 (3).有什么想法吗?
I looked around and it seems I need to use a PIVOT table. The only problem is that I found examples where an ordinal variable is used to create the columns. In this case case I have a predefined number of possible columns (3). Any ideas?
推荐答案
不需要多次自连接的解决方案:
A solution that does not require multiple self-joins:
您可以对 ROW_NUMBER() 分析函数的结果进行 PIVOT 以获得所需的输出:
You can PIVOT on the result of the ROW_NUMBER() analytic function to get your desired output:
SELECT *
FROM (
SELECT t.*,
ROW_NUMBER() OVER ( PARTITION BY test ORDER BY ROWNUM ) AS rn
FROM your_table t
)
PIVOT ( MAX( Reader ) AS reader, MAX( result ) AS result FOR rn IN ( 1, 2, 3 ) );
输出:
TEST 1_READER 1_RESULT 2_READER 2_RESULT 3_READER 3_RESULT
---- -------- -------- -------- -------- -------- --------
1 John 1.6 Jack 5.2
2 Ursula 2.5 Jack 5.1
3 Albert 3.0
或者，只使用聚合函数:
Or, just using aggregation functions:
SELECT test,
MAX( CASE rn WHEN 1 THEN reader END ) AS reader_1,
MAX( CASE rn WHEN 1 THEN result END ) AS result_1,
MAX( CASE rn WHEN 2 THEN reader END ) AS reader_2,
MAX( CASE rn WHEN 2 THEN result END ) AS result_2,
MAX( CASE rn WHEN 3 THEN reader END ) AS reader_3,
MAX( CASE rn WHEN 3 THEN result END ) AS result_3
FROM (
SELECT t.*,
ROW_NUMBER() OVER ( PARTITION BY test ORDER BY ROWNUM ) AS rn
FROM your_table t
)
GROUP BY test;
这篇关于具有非基值的数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！