3

I have this query in SQL Server 2005 (I used the more convenient 2008 insert method for example only) and I need the (null) to be replaced with 0 in the grid output.

Anyway to do this?

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
gooddadmike
  • 2,329
  • 4
  • 26
  • 48
  • 2
    Yes, in fact here are exact same questions/answers already on SO: [**1**](http://stackoverflow.com/questions/3297132/replace-null-values-in-sql-pivot), [**2**](http://stackoverflow.com/questions/9567807/null-in-sql-server-pivot), [**3**](http://stackoverflow.com/questions/10325538/how-to-replace-the-null-values-in-a-pivot-output-with-zeroes), [**4**](http://stackoverflow.com/questions/7304081/replacing-null-from-results-of-case-query). I searched for `sql server pivot replace nulls site:stackoverflow.com` on google. – mellamokb Jul 12 '12 at 19:42
  • #1 i had seen and even included in my fiddle. The others would have solved my issue. I guess google>so searchbox? – gooddadmike Jul 12 '12 at 21:20
  • That's what I tend to do. Because let's face it, google is almost always better at searching websites than the websites own search boxes :) – mellamokb Jul 13 '12 at 14:05
  • See http://meta.stackexchange.com/questions/140486 – Robert Harvey Jul 19 '12 at 23:53

1 Answers1

4

You would use the ISNULL() function. See SQL Fiddle 

SELECT 'lessonid          response ->'
   , isnull([0], 0) as [0]
  , isnull([1], 0) as [1]
  , isnull([2], 0) as [2]
  , isnull([3], 0) as [3]
  , isnull([4], 0) as [4]
FROM (
    SELECT lessonid AS 'lessonid          response ->'
        ,ISNULL(response,0) as response
        ,count(response) AS respcnt
    FROM tblRChoices
    GROUP BY lessonid
        ,response
    ) TableResponse
PIVOT(SUM(respcnt) FOR response IN (
            [0]
            ,[1]
            ,[2]
            ,[3]
            ,[4]
            )) ResponsePivot
Taryn
  • 242,637
  • 56
  • 362
  • 405