如何用單一SELECT 語法完成樞紐分析表,

SELECT p1.*, ( p1.Sum_a1+p1.Sum_a2+p1.Sum_b1) as Sum_All
from (select item,
    SUM(CASE class WHEN 'a1' THEN qty ELSE 0 END) AS Sum_a1,
    SUM(CASE class WHEN 'a2' THEN qty ELSE 0 END) AS Sum_a2,
    SUM(CASE class WHEN 'b1' THEN qty ELSE 0 END) AS Sum_b1
FROM testdata as p
GROUP BY item WITH ROLLUP
 ) as p1 


資料來源