SQL Server案例

Question

对于如何仅获取每个case id的非空值，有什么建议吗？

下面是SQL语句

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' 

我收到的输出是每个案例4行。请参阅附件中的图像。enter image description here

Answer 1

您可以使用诸如MAX()和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