sql - Pivot table with non-cardinal values

Question

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

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)

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?

Answer 1

A solution that does not require multiple self-joins:

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 ) );

Output:

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;