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

Please build a [db<>fiddle](https://sqlblog.org/fiddle); we can't see your data. — Aaron Bertrand, Apr 14 '22 at 19:59
Please provide example data and results so we understand what you are asking. — Dale K, Apr 14 '22 at 20:09
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Apr 14 '22 at 20:13
How would you do it in any other query? `select nullif([15],0) as [15], ...`? In this case you'd need to generate the list of nullif'd columns at the same time as the pivot table columns and use it to replace the `select *`. — AlwaysLearning, Apr 14 '22 at 22:29
Why can't the presentation tier just conditionally display `0` -> "blank"? What version of SQL Server are you using? Also: `CAST(@num AS nvarchar)` nope, don't do this; [always specify a length](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length). — Aaron Bertrand, Apr 15 '22 at 00:10

Aaron Bertrand · Answer 1 · 2022-04-15T01:36:40.340

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.

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

1 Answers1