Hide row with all null value using Dynamic Pivot

Question

I asked a question and the answer came up as I would have to use Dynamic Pivot Link to Question and I got a great helps. Now, I am trying to get rid of row with all null values but I could not.

*Please note the Column header for Current Result should Be Part 01, Part ABC, and Part 03 (not 0 as shown)

WHERE SubPart IS NOT NULL/ WHERE  Quantity IS NOT NULL 

but it does not work (well, it still queries but with null values)

After the FROM and from Statements

DECLARE @columns NVARCHAR(MAX)      
       ,@sql NVARCHAR(MAX)

SET @columns = N''
--Get column names for entire pivoting
SELECT @columns += N', ' + QUOTENAME(SpreadCol)
  FROM (select distinct Part_Number as SpreadCol 
        from test
       ) AS T
--select @columns
SET @sql = N'
SELECT SubPart, ' + STUFF(@columns, 1, 2, '') + '
FROM
(select SubPart , Part_Number as SpreadCol ,  Quantity
from test ) as D
PIVOT
(
  sum(Quantity) FOR SpreadCol IN ('
  + STUFF(REPLACE(@columns, ', [', ',['), 1, 1, '')
  + ')
) AS Pivot1
'
--select @sql

EXEC sp_executesql @sql

Answer 1

You need to add the where condition to the inner query which is used as the source for piovot and the column list query

DECLARE @columns NVARCHAR(MAX)      
       ,@sql NVARCHAR(MAX)

SET @columns = N''
--Get column names for entire pivoting
SELECT @columns += N', ' + QUOTENAME(SpreadCol)
  FROM (select distinct Part_Number as SpreadCol 
        from test WHERE  Quantity IS NOT NULL 
       ) AS T
--select @columns
SET @sql = N'
SELECT SubPart, ' + STUFF(@columns, 1, 2, '') + '
FROM
(select SubPart , Part_Number as SpreadCol ,  Quantity
from test WHERE  Quantity IS NOT NULL  ) as D
PIVOT
(
  sum(Quantity) FOR SpreadCol IN ('
  + STUFF(REPLACE(@columns, ', [', ',['), 1, 1, '')
  + ')
) AS Pivot1
'