sql - 通过提供 "not contained in aggregate function"(SQL-server) 自加入和分组

Question

美好的一天，这就是我想要做的..

ITEM   |   CATEGORY    |    AMOUNT
Sponge         A              29
Sponge         B              32
Sponge         C              33
Sponge         D              34
Rod            A               1
Rod            B               2
Rod            C               3
Rod            D               4

我希望输出看起来像这样:

ITEM    |   A   |  B   |   C   |   D
Sponge     29      32      33      34
Rod        1       2       3        5

我一直在尝试 GROUP BY 的各种组合，但我似乎无法让它工作。

Answer 1

SELECT ITEM
      ,MAX(CASE WHEN CATEGORY ='A' THEN Amount END) AS A
      ,MAX(CASE WHEN CATEGORY ='B' THEN Amount END) AS B
      ,MAX(CASE WHEN CATEGORY ='C' THEN Amount END) AS C
      ,MAX(CASE WHEN CATEGORY ='D' THEN Amount END) AS D
FROM YourTable
GROUP BY ITEM

或者

SELECT *
FROM YourTable
PIVOT (MAX(AMOUNT) FOR CATEGORY IN ([A],[B],[C],[D])) AS Pvt