0

I've been trying to remove NULL values from a pivot table. I've tried several suggested solutions to without luck.

    Declare @toName as nvarchar(max)

    SELECT *
    FROM (SELECT 
    isnull(protocol,0) as Protocol,     
    isnull(callCategory,0) as DCRCategory,
    isnull(DATEPART(Year, eCreationTime),0) AS Year, 
    isnull(DATENAME(MONTH, eCreationTime),0) [Month],
    isnull(COUNT(1),0) callCategory

    FROM DCR_DATA
    where ProjectManager = ''' + @toName + '''
    GROUP BY protocol, callCategory, YEAR(eCreationTime), DATENAME(MONTH, eCreationTime)) 


AS MontlyDCRData

PIVOT(SUM(callCategory)   
FOR Month IN ([January],[February],[March],[April],[May],
[June],[July],[August],[September],[October],[November],
[December])) AS MNamePivot

Here is an example of what I am returning: enter image description here

Protocol    DCRCategory Year    January February    March   April   May     June    July    August  September   October November    December
123         Cat 1       2017    NULL    NULL        NULL    NULL    NULL    NULL    NULL    NULL    4           NULL    NULL        NULL
Squirrel
  • 23,507
  • 4
  • 34
  • 32
stan musial
  • 1
  • I guess you want to replace with zero rather than remove? You can't "remove" a cell from a crosstab – Nick.Mc Sep 10 '19 at 11:38
  • Don't you mean `WHERE ProjectManager = @ToName`? Why is the parameter wrapped in 3 single quotes (`'''`)? – Thom A Sep 10 '19 at 11:40

2 Answers2

1
DECLARE @cols AS NVARCHAR(MAX),@cols1 AS NVARCHAR(MAX),@query AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(+'ISNULL('+CAST(ColName AS VARCHAR) +' ,0)'  ) + ' ' + QUOTENAME(ColName)
                    FROM TblName
                    GROUP BY ColName
                    ORDER BY ColName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


SELECT @cols1 = STUFF((SELECT ',' +  QUOTENAME(ColName)
                    from TblName
                    group by ColName
                    order by ColName
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


SET @query = 'SELECT ColName1,' + @cols + ' from 
             (
                SELECT ColName1, ColName2, ColName3
                FROM TblName
            ) x
            PIVOT 
            (
                SUM(ColName3)
                FOR ColName2 IN (' + @cols1 + ')
            ) p '

EXECUTE(@query);
Andrea
  • 11,801
  • 17
  • 65
  • 72
Sridhar
  • 11
  • 1
0

Not the most elegant solution, but here goes -> Select into a temp table

Declare @toName as nvarchar(max)

SELECT *
INTO #tmpTable
FROM (SELECT isnull(protocol,0) as Protocol
        , isnull(callCategory,0) as DCRCategory
        , isnull(DATEPART(Year, eCreationTime),0) AS Year
        , isnull(DATENAME(MONTH, eCreationTime),0) [Month]
        , isnull(COUNT(1),0) callCategory

    FROM DCR_DATA
    where ProjectManager = ''' + @toName + '''
    GROUP BY    protocol
    ,           callCategory
    ,           YEAR(eCreationTime)
    ,           DATENAME(MONTH, eCreationTime))


    AS MontlyDCRData

    PIVOT(SUM( callCategory)
    FOR Month IN ([January],[February],[March],[April],[May],
    [June],[July],[August],[September],[October],[November],
    [December])) AS MNamePivot

and then isnull from there.

SELECT tmp.Protocol, tmp.DCRCategory, tmp.[Year]
    , isnull(tmp.January,0)
    , ... 
    , isnull(tmp.December,0)
FROM #tmpTable as tmp
Rey
  • 186
  • 5