How do I remove NULL values from a dynamic pivot output

Question

I'm running into an issue where the output for my dynamic pivot contains thousands of NULL values that I would like to replace with 0. I am not able to use ISNULL before the pivot is generated because there aren't any NULL values in the original table. I've searched everywhere and I havn't located anything that works for me. Here is my code.

DECLARE @col NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

SET @col =  (SELECT STRING_AGG(CONVERT(NVARCHAR(MAX),CONCAT('[',[order_date],']')),',') FROM
            (
            SELECT DISTINCT TOP 10000 (CAST(CONVERT(datetimeoffset,[purchase-date],127) AS DATE)) AS order_date
            FROM dbo.Amazon_FBAFulfilledShipments_Report
            ORDER BY order_date ASC
            ) trial_table);
SELECT @col
PRINT @col
SET @sql = 
'SELECT ship_country,sku,' + @col + '
FROM
(
SELECT [ship-country] AS ship_country,sku,CAST([quantity-shipped] as INT) AS quantity_shipped,CAST(CONVERT(datetimeoffset,[purchase-date],127) AS DATE) AS order_date
FROM dbo.Amazon_FBAFulfilledShipments_Report
) as Source_table
PIVOT
(SUM(quantity_shipped) FOR order_date IN (' + @col +')
)AS pivot_table'

PRINT @sql
EXECUTE (@sql)

Answer 1

The key is to think about it this way: you need two different sets of columns to jam into the dynamic SQL: one for the pivot (FOR col IN ([day1], [day2], ...)) and one for the output (day1 = COALESCE(day1, 0), day2 = COALESCE(day2, 0), ...). This previous question and this article may be useful.

Here's a working script (and here's an example db<>fiddle).

DECLARE @OutputCols nvarchar(max),
        @PivotCols  nvarchar(max),
        @sql        nvarchar(max),
        @StartingDate date = '20180101';

;WITH src(d) AS
(
  SELECT CONVERT(nvarchar(max), QUOTENAME(CONVERT(date, [purchase-date])), 120)
    FROM dbo.Amazon_FBAFulfilledShipments_Report
    WHERE [purchase-date] >= @StartingDate
    GROUP BY CONVERT(date, [purchase-date])
)
SELECT @PivotCols  = STRING_AGG(d, ','),
       @OutputCols = STRING_AGG(CONCAT(d, ' = COALESCE(', d, ', 0)'), ',')
FROM src;

SET @sql = N'SELECT ship_country,sku,' + @OutputCols + '
FROM
(
  SELECT [ship-country] AS ship_country,sku,
    CAST([quantity-shipped] as INT) AS quantity_shipped,
    CAST(CONVERT(datetimeoffset,[purchase-date],127) AS DATE) AS order_date
  FROM dbo.Amazon_FBAFulfilledShipments_Report
  WHERE [purchase-date] >= @StartingDate
) AS Source_table 
PIVOT
(
  SUM(quantity_shipped) FOR order_date IN (' + @PivotCols +')
) AS pivot_table'

PRINT @sql;
EXEC sys.sp_executesql @sql, N'@StartingDate date', @StartingDate;

Multiple things:

• I got rid of the DISTINCT TOP 10000. If you want "current" data, just use a WHERE clause (which will produce more predictable results, like since Jan 2018, not since 10,000 distinct purchase date values ago), and use it in both queries (there's no sense limiting your PIVOT list to 10,000 dates if your dynamic query queries ALL OF TIME, though I'm not sure how you'd consume such a query anyway).
• Use QUOTENAME(); it is much safer and less tedious than trying to use string concatenation to add your own [ and ]. Dynamic SQL probably isn't on the radar in this specific scenario, but it is not a good practice and sets a bad example.
• Don't use EXECUTE(), always use sys.sp_executesql.
• Dynamic SQL advice here.