Dynamic Pivot without Null value

问题:

This is a dynamic crosstab query in Northwind database:

DECLARE @COUNTRY NVARCHAR(MAX) ='', @COUNTRY2 NVARCHAR(MAX)

SELECT @COUNTRY = @COUNTRY + QUOTENAME(Country)+', '
FROM Customers
GROUP BY Country

SET @COUNTRY= LEFT(@COUNTRY, LEN(@COUNTRY)-1)

SET @COUNTRY2 = REPLACE(@COUNTRY, ',' , '+')

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT * , '+@COUNTRY2+' AS TOTAL
           FROM (SELECT E.EmployeeID, E.LastName, 
                        ISNULL( OD.Quantity, 0)* ISNULL(OD.[UnitPrice],0) QU,
                        O.ShipCountry AS CO
                 FROM Orders O JOIN Employees E ON O.EmployeeID = E.EmployeeID
                      JOIN [dbo].[Order Details] OD ON OD.OrderID = O.OrderID) AS T
PIVOT(SUM(QU) FOR CO IN ('+@COUNTRY+')) AS PVT
ORDER BY 1'
EXEC(@SQL) 

I need to change the code in a way that have Null values replaced by 0.

回答1:

DECLARE @COUNTRY NVARCHAR(MAX) = '' ,
  @COUNTRY2 NVARCHAR(MAX);

SELECT  @COUNTRY = @COUNTRY + COALESCE(QUOTENAME(Country) + ', ', '')
FROM    Customers
WHERE   EXISTS ( SELECT *
                 FROM   [Orders] AS [o]
                 WHERE  o.[CustomerID] = Customers.[CustomerID] )
GROUP BY Country;

SET @COUNTRY = LEFT(@COUNTRY, LEN(@COUNTRY) - 1);

SET @COUNTRY2 = REPLACE(@COUNTRY, ',', '+');

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * , ' + @COUNTRY2 +
  ' AS TOTAL
           FROM (
           SELECT  oe.EmployeeID, oe.LastName, oe.ShipCountry AS CO,
        COALESCE(OD.Quantity * OD.UnitPrice, 0) AS QU
FROM    (
          SELECT  EmployeeID, LastName, ShipCountry
          FROM    (
                    SELECT DISTINCT
                            ShipCountry
                    FROM    Orders
                  ) o ,
                  Employees
        ) oe
LEFT JOIN Orders O ON O.EmployeeID = oe.EmployeeID AND
                  [oe].[ShipCountry] = [O].[ShipCountry]
LEFT JOIN [Order Details] OD ON OD.OrderID = O.OrderID 
           ) AS T
PIVOT(SUM(QU) FOR CO IN (' + @COUNTRY + ')) AS PVT
ORDER BY 1';

EXEC(@SQL); 

回答2:

You need to change the SELECT * to:

SELECT ISNULL(Argentina,0) AS 'Argentina' , INSNULL(Belgium,0) AS 'Belgium' , ....

Ofcourse, you would need to change your dynamic query to reflect the ISNULL functions.

Good Luck