美好的一天,
这就是我想要做的……
这就是我想要做的……
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的各种组合,但我似乎无法让它工作.
解决方法
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
