Clever Geek Handbook

Remove NULLS from dynamic query

I am unable to remove zeros from the result of a dynamic query.

Here's an example of what will be in table # t3:

BornDate    |  ClickDate    |  Clicks
10/23/2014  |  11/19/2014   |  25
10/23/2014  |  11/18/2014   |  6
10/23/2014  |  11/20/2014   |  5
10/23/2014  |  11/22/2014   |  17
10/23/2014  |  11/23/2014   |  11
10/24/2014  |  11/19/2014   |  1
10/24/2014  |  11/18/2014   |  6
10/24/2014  |  11/20/2014   |  3
10/24/2014  |  11/21/2014   |  3
10/24/2014  |  11/23/2014   |  2

So my question is, how do I remove the values NULL

when running the next query?

Here's my request

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(ClickDate)
FROM (SELECT DISTINCT ClickDate FROM #t3 ) AS ClickDate order by ClickDate

--Prepare the PIVOT query using the dynamic 

SET @DynamicPivotQuery = 
  'SELECT BornDate, ' + @ColumnName + '  
    FROM #t3 
    PIVOT (SUM(Clicks) 
          FOR ClickDate IN (' + @ColumnName + ')) AS PVTTable order by 1, 2'

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

Query result:

| BORNDATE   | 2014-11-18 | 2014-11-19 | 2014-11-20 | 2014-11-21 | 2014-11-22 | 2014-11-23|
|------------|------------|------------|------------|------------|------------|-----------|
| 2014-10-23 |          6 |         25 |          5 |     (null) |         17 |        11 |
| 2014-10-24 |          6 |          1 |          3 |          3 |     (null) |         2 |

You see NULLS on row 10/23/2014 for column 11/21/2014 ... and again for row 10/24/2014 in column for 11/22/2014. I want to replace those zeros.

+3


source to share


2 answers


Instead of using it @ColumnName

to make both the list of values ​​become new columns and the final selection list, you need to create a separate list of column names to replace null

with zero - similar to isnull(yourcol, 0) as yourcol

.

I usually use FOR XML

it STUFF

to concatenate column names as well, so you can use:

--Get null replacements of the PIVOT Column 
select @NullName = STUFF((SELECT ', IsNull(' + QUOTENAME(ClickDate)+', 0) as '+QUOTENAME(ClickDate)
                    from #t3
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

This creates the second second of the column names to be used for the final picklist. Then your PIVOT code will look like this:



DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @NullName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(ClickDate)
FROM (SELECT DISTINCT ClickDate FROM #t3 ) AS ClickDate order by ClickDate

--Get null replacements of the PIVOT Column 
select @NullName = STUFF((SELECT ', IsNull(' + QUOTENAME(ClickDate)+', 0) as '+QUOTENAME(ClickDate)
                    from #t3
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

--Prepare the PIVOT query using the dynamic 

SET @DynamicPivotQuery = 
  'SELECT BornDate, ' + @NullName + '  
    FROM #t3 
    PIVOT (SUM(Clicks) 
          FOR ClickDate IN (' + @ColumnName + ')) AS PVTTable order by 1, 2'

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

See SQL Fiddle with Demo . This gives the final output:

| BORNDATE   | 2014-11-18 | 2014-11-19 | 2014-11-20 | 2014-11-21 | 2014-11-22 | 2014-11-23|
|------------|------------|------------|------------|------------|------------|-----------|
| 2014-10-23 |          6 |         25 |          5 |          0 |         17 |        11 |
| 2014-10-24 |          6 |          1 |          3 |          3 |          0 |         2 |
+4


source


To replace zeros with zero:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @ColumnNameSelect AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(ClickDate)
FROM (SELECT DISTINCT ClickDate FROM t3 ) AS ClickDate order by ClickDate

--Get distinct values of the PIVOT Column with isnull for zero values
SELECT @ColumnNameSelect= ISNULL(@ColumnNameSelect + ',','') 
       + 'isnull(' + QUOTENAME(ClickDate) + ',0) as ' + QUOTENAME(ClickDate)
FROM (SELECT DISTINCT ClickDate FROM t3 ) AS ClickDate order by ClickDate


--Prepare the PIVOT query using the dynamic 

SET @DynamicPivotQuery = 
  'SELECT BornDate, ' + @ColumnNameSelect + '  
    FROM t3 
    PIVOT (SUM(Clicks) 
          FOR ClickDate IN (' + @ColumnName + ')) AS PVTTable order by 1, 2'

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery


SQL FIDDLE

+3


source






More articles:


All Articles