Is there a way to leave out the zeros in this pivot table?

Question

I dynamically create a pivot table based on a variable I create to account for the number of weeks.

I then used a derived table to only include weeks that actually have data in them (those weeks become my columns in the pivot table)

I Need to eliminate all the zeros in the pivot table and just leave the row blank where there is a zero.

DECLARE @cols nvarchar(MAX) = ''
DECLARE @num int = 1

WHILE @num < 53
BEGIN
IF @num IN (SELECT dt.[Week] FROM 
                            (   SELECT 
                                    DATEPART(WEEK, r.RegistrationDate) as [Week]
                                FROM dbo.Registration r
                                LEFT JOIN dbo.RegistrationType rt
                                    ON r.RegistrationTypeID = rt.RegistrationTypeID
                                GROUP BY DATEPART(WEEK, r.RegistrationDate), YEAR(r.RegistrationDate), rt.RegistrationType, DATEPART(DW, r.RegistrationDate)
                                HAVING YEAR(RegistrationDate) = 2021 AND SUM(CASE WHEN DATEPART(WEEKDAY, r.RegistrationDate) = 1 THEN 1 ELSE 0 END) != 0
                            ) dt 
        ) 
SELECT @cols += QUOTENAME(CAST(@num AS nvarchar)) + ','
SET @num +=1
END

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

DECLARE @pivotQuery nvarchar(MAX)
SET @pivotQuery = 
'
SELECT * FROM
(
    SELECT
        DATEPART(WEEK, r.RegistrationDate)  as [Week],
    rt.RegistrationType,
    r.RegistrationID
FROM dbo.Registration r
LEFT JOIN dbo.RegistrationType rt
    ON r.RegistrationTypeID = rt.RegistrationTypeID
WHERE YEAR(RegistrationDate) = 2021 AND DATEPART(WEEKDAY, r.RegistrationDate) = 1
) AS SourceTable
PIVOT 
(
COUNT(RegistrationID)
FOR [Week] IN (' + @cols + ')
) as PivotTable
'
EXEC (@pivotQuery)

This is the results. I just want to leave a blank space everywhere that there is a zero

Answer 1

While at a conceptual level I think it's odd to make SQL Server do this - can't your presentation tier simply replace 0 with an empty string? That's certainly where I'd prefer to do it, because it's kind of sloppy in T-SQL. Let's forget the PIVOT and dynamic SQL at all, and just focus on how to get the result you want from a basic query returning integers.

;WITH src(w) AS 
(
  SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2
)
SELECT w, 
  wAdjusted = COALESCE(NULLIF(CONVERT(varchar(11),w),'0'),'') 
FROM src;

Output:

w     wAdjusted
----  ---------
0
1     1
2     2

• Example db<>fiddle showing why the expression has to be even more complex than you'd expect.

In any case, you shouldn't be querying the table 53 times in a loop to see if these numbers are there - why not just query once and use string aggregation? You're currently building the pivot columns in a loop but you'll need to also build expression columns. You can do this all in one pass of the table instead of 53, e.g. in SQL Server 2017+:

DECLARE @PivotCols nvarchar(max) = N'',
        @ExprCols  nvarchar(max) = N'';

SELECT @PivotCols = STRING_AGG(wk, ','),
       @ExprCols  = STRING_AGG(CONCAT(wk,
         N'=COALESCE(NULLIF(CONVERT(varchar(11),',
         wk,',''0''),'''')'), ',')
FROM
(
  SELECT wk = QUOTENAME(CONVERT(varchar(11), [Week])) FROM 
  ( 
    SELECT dt.[Week] FROM ...
    ...your query to get weeks from Registration here...
  ) AS w
) AS w;

DECLARE @pivotQuery nvarchar(max) = N'
  SELECT RegistrationType, ' + @ExprCols 
  + N' FROM 
  (
    SELECT DATEPART(WEEK, ...
    ...your query to feed pivot here...
  ) AS SourceTable
  PIVOT
  (
    COUNT(RegistrationID) FOR [Week] IN 
    (' + @PivotCols + ')
  ) as PivotTable;';

EXEC sys.sp_executesql @pivotQuery;

This is a little more cumbersome in dustier, less optimal versions; you'll need FOR XML PATH as I describe here. Please always specify the minimum version of SQL Server you need to support so you can get accurate, relevant solutions.