Change Null value to 0 in Dynamic pivoted query

• mjohnson 71479

  SSC Eights!

  Points: 846

  More actions

  July 24, 2017 at 9:47 am

  #327673

  I have the following SPROC which is used inside another procedure
  
ALTER PROCEDURE [dbo].[AverageDemographicGroupAnswer]

  @GroupNumber int,
@SurveyNumber int,
@Demographic int

  AS

  SET NOCOUNT ON

  DECLARE @CompanyName nvarchar(100)

  SELECT
    @CompanyName = CompanyName
FROM
    dbo.Survey
WHERE
    SurveyNumber = @SurveyNumber

  DECLARE @Temp table(
PNUM int,
DemAnswer int,
ResponseText varchar(200),
tid int identity
)

  INSERT @Temp    
    SELECT A.PNum, A.Answer, '' FROM Answers A WHERE A.QuestionID = @Demographic
   
    DECLARE @Start int, @Stop int, @Answer int, @response varchar(200)
    SET @Start = 1
    SELECT @Stop = Count(*) + 1 FROM @Temp
WHILE @Start < @Stop    
    BEGIN
        SELECT @Answer = DemAnswer FROM @Temp WHERE tid = @Start
        SELECT @Response = REsponseTExt FROM SurveyResponses WHERE QuestionID = @Demographic AND Sequence = @Answer
        UPDATE @Temp SET REsponseText = @response WHERE tid = @Start

          SET @Start = @Start + 1
    END

  DECLARE @Temp2 TABLE
(
Qid int,
QuestionText nvarchar(400),
Answer int,
DemAnswer int,
ResponseText varchar(200),
tid int identity
)
INSERT @Temp2
    SELECT Q.QuestionID, Q.QuestionText, Cast(A.Answer as int), T.DemAnswer, T.ResponseText
    FROM SurveyQuestions Q INNER JOIN dbo.Answers A ON Q.QuestionID = A.QuestionID INNER JOIN @Temp T ON A.PNum = T.PNUM
    WHERE Q.Qtype = 1 and GroupNumber = @GroupNumber AND Q.SurveyNumber = @SurveyNumber AND A.PNum IN (SELECT PNum FROM dbo.Participants WHERE SurveyNumber = @SurveyNumber)

  SELECT REPLACE(QuestionText,'#Company#',@CompanyName) AS QuestionText, ResponseText, Cast(AVG(CAST(Answer as decimal(38,4))) as Decimal(38,2)) AS AverageAnswer FROM @Temp2 GROUP BY QuestionText, Qid, ResponseText, DemAnswer ORDER BY Qid desc, DemAnswer

  This produces the following data
  

  The query to pivot the data
  
ALTER PROCEDURE [dbo].[PivotedDemographicGroupAverage]

  @GroupID int,
@Survey int,
@Demographic int

  AS

  SET NOCOUNT ON

  CREATE TABLE #Data
(
    QuestionText varchar(400),
    ResponseText varchar(200),
    AverageAnswer float
    
)
INSERT #Data
    EXECUTE AverageDemographicGroupAnswer @GroupID, @Survey, @Demographic

  --SELECT * FROM #Data
DECLARE @PivotQuery NVARCHAR(MAX)
DECLARE @Cols varchar(max)
SET @Cols = STUFF((SELECT ',' + QUOTENAME(ResponseText,'[]') FROM dbo.SurveyResponses WHERE QuestionID = @Demographic ORDER BY [Sequence] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
--SELECT @Cols
    
SET @PivotQuery = 'SELECT QuestionText, '+ @Cols +' FROM
    (
        SELECT QuestionText, ResponseText, AverageAnswer FROM #Data
    )x
    PIVOT
    ( MAX(AverageAnswer) FOR ResponseText in (' + @Cols + ')
    )p'
--select @PivotQuery
EXECUTE(@PivotQuery)

  DROP TABLE #Data


  which  produces
  

  I need to replace the NULL  with 0   but not sure how to do so in this instance.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 24, 2017 at 10:06 am

  #1952413

  It's possible to do it with PIVOT, but you need 2 different variables for your columns. Instead, I recommend you to use cross tabs which can give better performance. You should be cautious when using QUOTENAME(). In strings larger than 128 characters, the function will return a NULL.
  
DECLARE @PivotQuery NVARCHAR(MAX)
DECLARE @Cols varchar(max)
SET @Cols = (SELECT CHAR(13) + CHAR(9) + ',MAX(CASE WHEN ResponseText = ' + QUOTENAME(ResponseText,'''') + ' THEN AverageAnswer ELSE 0 END) AS ' + QUOTENAME(ResponseText)
       FROM dbo.SurveyResponses
       WHERE QuestionID = @Demographic
       ORDER BY [Sequence] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
--SELECT @Cols
 
SET @PivotQuery = 'SELECT QuestionText '+ @Cols +'
FROM #Data
GROUP BY QuestionText'

  --select @PivotQuery
EXECUTE(@PivotQuery)


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• mjohnson 71479

  SSC Eights!

  Points: 846

  More actions

  July 24, 2017 at 10:15 am

  #1952419

  Thank You!!

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 24, 2017 at 11:56 am

  #1952438

  mjohnson 71479 - Monday, July 24, 2017 10:15 AM

  Thank You!!

  You're welcome.
  Do you understand how this works?

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• mjohnson 71479

  SSC Eights!

  Points: 846

  More actions

  July 24, 2017 at 12:41 pm

  #1952444

  I believe so.  The CASE statement says if there is no value to enter 0