Access SQL pivot result merge columns

Question

I am trying to put data from an Access table in a different format by using a PIVOT query. I have searched the entire internet, but I can't really get the desired result. This is what I have got so far..

The original table:

+----+-------+
| ID | Value |
+----+-------+
|  2 |     2 |
|  3 |     3 |
|  4 |     4 |
|  5 |     5 |
|  6 |     6 |
|  6 |     7 |
|  6 |     8 |
|  7 |     9 |
|  8 |    10 |
|  9 |    11 |
| 12 |    12 |
| 13 |     6 |
| 13 |     8 |
| 14 |     9 |
| 15 |    10 |
| 16 |    11 |
| 20 |    12 |
+----+-------+

When applying the following query:

TRANSFORM Max(Value)
SELECT ID
FROM tempTable
GROUP BY ID
PIVOT Value
;

I get:

+----+---+---+---+---+---+---+---+---+----+----+----+
| ID | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
+----+---+---+---+---+---+---+---+---+----+----+----+
|  2 | 2 |   |   |   |   |   |   |   |    |    |    |
|  3 |   | 3 |   |   |   |   |   |   |    |    |    |
|  4 |   |   | 4 |   |   |   |   |   |    |    |    |
|  5 |   |   |   | 5 |   |   |   |   |    |    |    |
|  6 |   |   |   |   | 6 | 7 |   | 9 |    |    |    |
|  7 |   |   |   |   |   |   |   | 9 |    |    |    |
|  8 |   |   |   |   |   |   |   |   | 10 |    |    |
|  9 |   |   |   |   |   |   |   |   |    | 11 |    |
| 12 |   |   |   |   |   |   |   |   |    |    | 12 |
| 13 |   |   |   |   | 6 |   |   |   |    |    |    |
| 13 |   |   |   |   |   |   | 8 |   |    |    |    |
| 14 |   |   |   |   |   |   |   | 9 |    |    |    |
| 15 |   |   |   |   |   |   |   |   | 10 |    |    |
| 16 |   |   |   |   |   |   |   |   |    | 11 |    |
| 20 |   |   |   |   |   |   |   |   |    |    | 12 |
+----+---+---+---+---+---+---+---+---+----+----+----+

What I would like to get:
(There are max 4 different values at the moment, but there might be more in the future)

+----+--------+--------+--------+--------+
| ID | Value1 | Value2 | Value3 | Value4 |
+----+--------+--------+--------+--------+
|  2 |      2 |        |        |        |
|  3 |      3 |        |        |        |
|  4 |      4 |        |        |        |
|  5 |      5 |        |        |        |
|  6 |      6 |      7 |      9 |        |
|  7 |      9 |        |        |        |
|  8 |     10 |        |        |        |
|  9 |     11 |        |        |        |
| 12 |     12 |        |        |        |
| 13 |      6 |      8 |        |        |
| 14 |      9 |        |        |        |
| 15 |     10 |        |        |        |
| 16 |     11 |        |        |        |
| 20 |     12 |        |        |        |
+----+--------+--------+--------+--------+

I really hope some fantastic Access SQL hero can help me out here! Thanks in advance for any help!

Answer 1

We can assign a "Value'n'" rank order to each row by using the following query

SELECT t1.ID, t1.Value, "Value" & COUNT(*) AS ValueRank
FROM
    tempTable AS t1
    INNER JOIN
    tempTable AS t2
        ON t2.ID = t1.ID AND t2.Value <= t1.Value
GROUP BY t1.ID, t1.Value

That query returns

ID  Value  ValueRank
--  -----  ---------
 2      2  Value1   
 3      3  Value1   
 4      4  Value1   
 5      5  Value1   
 6      6  Value1   
 6      7  Value2   
 6      8  Value3   
 7      9  Value1   
 8     10  Value1   
 9     11  Value1   
12     12  Value1   
13      6  Value1   
13      8  Value2   
14      9  Value1   
15     10  Value1   
16     11  Value1   
20     12  Value1   

So we'll just do our crosstab query on that

TRANSFORM Max(Value) AS MaxOfValue
SELECT ID
FROM
    (
        SELECT t1.ID, t1.Value, "Value" & COUNT(*) AS ValueRank
        FROM
            tempTable AS t1
            INNER JOIN
            tempTable AS t2
                ON t2.ID = t1.ID AND t2.Value <= t1.Value
        GROUP BY t1.ID, t1.Value
    )
GROUP BY ID
PIVOT ValueRank

returning

ID  Value1  Value2  Value3
--  ------  ------  ------
 2       2                
 3       3                
 4       4                
 5       5                
 6       6       7       8
 7       9                
 8      10                
 9      11                
12      12                
13       6       8        
14       9                
15      10                
16      11                
20      12