下面是我用來創建資料透視表的 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
uj5u.com熱心網友回復：
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/491560.html
上一篇：對SQL上的2列進行計數