Using TSQL, in a subquery select statement I want to return a row for a column as NULL-CodePudding

        WHEN qt.Id = 10 THEN
             (
             SELECT 
                 OptionId = NULL
                 ,AnswerString = NULL
                 ,san.AnswerNumber as AnswerBinary
                 ,Total = COUNT(*) 
             FROM dbo.SurveyAnswers AS san
             WHERE san.QuestionId = sq.Id
             GROUP BY san.AnswerNumber
             FOR JSON AUTO
             )

So in the above subquery, how could I force SQL to return a column named OptionId that would be NULL

CodePudding user response：

If the problem is that OptionId does not appear in the row because of NULL value. then add this option:

FOR JSON AUTO, INCLUDE_NULL_VALUES

INCLUDE_NULL_VALUES cause that the keys with null value, to include in JSON.