CODEWITHSUNDEEP
sqlsql-servercasedistinct-values
Delphiniumd
Delphiniumd

Reputation: 1

SQL Server Case

Any suggestions on how to get only the not null values for each case id?

Here is the SQL statement

SELECT  
    c.record_id,
    CASE WHEN a.Question_Key = 'AA1.DQ6.EF10' THEN (b.selection_value) end as Q1,
    CASE WHEN a.Question_Key = 'AA1.DQ6.EF11' THEN (b.selection_value) end as Q2,
    CASE WHEN a.Question_Key = 'AA1.DQ6.EF12' THEN (b.selection_value) end as Q3,
    CASE WHEN a.Question_Key = 'AA1.DQ6.EF13' THEN (b.selection_value) end as Q4,
    CASE WHEN a.Question_Key = 'AA1.DQ6.EF14' THEN (b.selection_value) end as Q5,
    CASE WHEN a.Question_Key = 'AA1.DQ6.EF15' THEN (b.selection_value) end as Q6,
    CASE WHEN a.Question_Key = 'AA1.DQ6.EF16' THEN (b.selection_value) end as Q7
FROM 
    COL_CASE_Case c 
INNER JOIN
    COL_SURV_Survey s ON c.Case_Key = s.Parent_Record_Key 
INNER JOIN 
    COL_SURV_Survey_Answers b ON s.Survey_Key = b.Survey_Key 
INNER JOIN 
    COL_SURV_Survey_Questions a ON b.Question_Key = a.Question_Key 
WHERE 
    a.Question_Type = 0 AND c.record_id = 'CASE-0002999'

The output I am recieving is 4 rows for each case. Please see attached image. enter image description here

Upvotes: 0

Views: 58

Answers (1)

Hart CO
Hart CO

Reputation: 34774

You can use an aggregate like MAX() and a GROUP BY:

SELECT  c.record_id,
      MAX(CASE WHEN  a.Question_Key = 'AA1.DQ6.EF10' THEN (b.selection_value)  end)   as Q1,
      MAX(CASE WHEN  a.Question_Key = 'AA1.DQ6.EF11' THEN (b.selection_value)  end) as Q2,
      MAX(CASE WHEN  a.Question_Key = 'AA1.DQ6.EF12' THEN (b.selection_value)  end) as Q3,
      MAX(CASE WHEN  a.Question_Key = 'AA1.DQ6.EF13' THEN (b.selection_value)  end) as Q4,
      MAX(CASE WHEN  a.Question_Key = 'AA1.DQ6.EF14' THEN (b.selection_value)  end) as Q5,
      MAX(CASE WHEN  a.Question_Key = 'AA1.DQ6.EF15' THEN (b.selection_value)  end) as Q6,
      MAX(CASE WHEN  a.Question_Key = 'AA1.DQ6.EF16' THEN (b.selection_value)  end) as Q7
FROM COL_CASE_Case c inner join COL_SURV_Survey s on c.Case_Key = s.Parent_Record_Key 
 inner join COL_SURV_Survey_Answers b on s.Survey_Key = b.Survey_Key 
 inner join COL_SURV_Survey_Questions a on b.Question_Key = a.Question_Key 
WHERE a.Question_Type = 0 and c.record_id = 'CASE-0002999' 
GROUP BY c.record_id

Upvotes: 4

Related Questions