在透视表中使用SUM函数

Question

下面是我用来创建数据透视表的SQL。我需要用'0'替换空值，但是，当试图在枢轴内使用case表达式时，我得到了一个错误。这难道不可能吗?

SELECT *
FROM
(
SELECT c.CompanyName, p.ProductName, od.Quantity
FROM  Customers as c INNER JOIN
Orders as o ON c.CustomerID = o.CustomerID INNER JOIN
[Order Details] as od ON o.OrderID = od.OrderID INNER JOIN
Products as p on od.ProductID = p.ProductID
) as QuantityOrdered
pivot
(
(CASE
WHEN SUM(Quantity) is NULL THEN 0
ELSE SUM(Quantity)
END)
for ProductName in ([Alice Mutton],    
[Aniseed Syrup], 
[Boston Crab Meat], 
[Camembert Pierrot], 
[Carnarvon Tigers])
) as crossTabTable
order by CompanyName

Answer 1

PIVOT只允许简单的聚合函数和单列，不允许其他表达式。

你可以用条件聚合来代替，这更啰嗦，但更灵活。

SELECT
c.CompanyName,
[Alice Mutton]      = ISNULL(SUM(CASE WHEN p.ProductName = 'Alice Mutton'      THEN od.Quantity END), 0),
[Aniseed Syrup]     = ISNULL(SUM(CASE WHEN p.ProductName = 'Aniseed Syrup'     THEN od.Quantity END), 0),
[Boston Crab Meat]  = ISNULL(SUM(CASE WHEN p.ProductName = 'Boston Crab Meat'  THEN od.Quantity END), 0),
[Camembert Pierrot] = ISNULL(SUM(CASE WHEN p.ProductName = 'Camembert Pierrot' THEN od.Quantity END), 0),
[Carnarvon Tigers]  = ISNULL(SUM(CASE WHEN p.ProductName = 'Carnarvon Tigers'  THEN od.Quantity END), 0)
FROM  Customers as c
INNER JOIN Orders as o ON c.CustomerID = o.CustomerID
INNER JOIN [Order Details] as od ON o.OrderID = od.OrderID
INNER JOIN Products as p on od.ProductID = p.ProductID
GROUP BY
c.CompanyName
ORDER BY
c.CompanyName;