I need a Case Statement where I can choose the answers depending on if the Answer column has a NULL value or not.

For example if one of the rows has a NULL in Answer, i would only choose the names showing up that is not Null. And would like it to display like "Clark Kent And Lois Lane".

If there was just one name in the Answer column then i would like it just show "Clark Kent"

CodePudding user response：

Try this Query:

select t.EncounterID, 
STUFF((
SELECT (' and '    isnull([Answer],'') ) ) 
FROM [TableName] t2
where t2.EncounterID=t.EncounterID  
FOR XML PATH('')), 1, 4, '')
from [TableName] t
group by t.EncounterID

CodePudding user response：

You can use STRING_AGG within a CTE, then RIGHT JOIN the CTE to your table and use a CASE statement to check for NULL values, replacing it with the STRING_AGG result from the CTE like this:

WITH cte AS (SELECT a.EncounterID, 
                    STRING_AGG(a.Answer, ' and ') AS Concat_Column
            FROM sample_table a
             GROUP BY a.EncounterID)
SELECT  
  c.QuestionID,
  c.EncounterID,
  CASE WHEN c.Answer IS NULL THEN d.Concat_Column
       ELSE c.Answer
  END AS Answer
  FROM cte d
  RIGHT JOIN sample_table c ON d.EncounterID = c.EncounterID AND c.Answer IS NULL

Optionally, you can use a subquery to get the same result:

SELECT c.QuestionID,
       c.EncounterID, 
       CASE WHEN c.Answer IS NULL THEN b.Answer
              ELSE c.Answer
         END AS Answer
FROM sample_table c 
LEFT JOIN (SELECT a.EncounterID, 
                  STRING_AGG(a.Answer, ' and ') AS Answer
           FROM sample_table a
           GROUP BY a.EncounterID) b
           ON b.EncounterID = c.EncounterID AND c.Answer IS NULL

Input:

Output:

db<>fiddle here.

Also, if you want to remove the duplicate names (which would also remove unique QuestionID's) you can add the ROW_NUMBER() window function to the outer SELECT statement which will assign a sequential integer based on the EncounterID column and the Answer column from the STRING_AGG function. Then, wrap the outer query in parentheses to create another subquery, selecting only values WHERE row number = 1 to get the distinct Answer rows.

SELECT d.QuestionID,
       d.EncounterID,
       d.Answer
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY c.EncounterID, 
                CASE WHEN c.Answer IS NULL THEN b.Answer 
                     ELSE c.Answer 
                END ORDER BY c.EncounterID) as rn,
             c.QuestionID,
             c.EncounterID, 
             CASE WHEN c.Answer IS NULL THEN b.Answer
                  ELSE c.Answer
             END AS Answer
       FROM sample_table c 
       LEFT JOIN (SELECT a.EncounterID, 
                         STRING_AGG(a.Answer, ' and ') AS Answer
                  FROM sample_table a
                  GROUP BY a.EncounterID) b
                  ON b.EncounterID = c.EncounterID AND c.Answer IS NULL) d
WHERE d.rn = 1

Output:

db<>fiddle here.