Use SUM function inside a Pivot table-CodePudding

Below is the SQL I use to create my pivot table. I need to replace null values with '0', however, I get an error when trying to use a case expression inside the pivot. Is this not possible?

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

CodePudding user response：

PIVOT only allows simple aggregation functions and a single column, no other expressions.

You can instead use conditional aggregation, which is more wordy but much more flexible.

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;