访问SQL枢轴结果合并列

Question

我试图通过使用PIVOT查询将访问表中的数据以不同的格式放置。我已经搜索了整个互联网，但我不能真正得到想要的结果。这就是我目前所得到的..。

原表：

+----+-------+
| 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 |
+----+-------+

应用以下查询时：

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 |
+----+---+---+---+---+---+---+---+---+----+----+----+

我想要的

(目前有最多4个不同的值，但将来可能会更多)

+----+--------+--------+--------+--------+
| 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 |        |        |        |
+----+--------+--------+--------+--------+

我真的希望一些奇妙的访问SQL英雄可以帮助我在这里！提前感谢您的帮助！

Answer 1

通过使用以下查询，我们可以为每一行分配一个“值‘n”的秩顺序

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

该查询返回

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   

因此，我们只需对此进行交叉表查询

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

返回

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