sql - 每条记录都有一个新行

Question

我希望结果在满足条件的每个实例中生成一个新行。我正在使用 CASE 语句，但这不是要走的路，因为一旦满足第一个条件，它就会停止评估该字段。

SELECT 
Reviews.ReviewID, 
CASE 
    WHEN Score_CorrectID = 0 THEN 'Correct ID Right Party Authentication'  
    WHEN Score_ProperlyIdentified = 0 THEN 'PCA Properly Identified Itself'
    WHEN Score_MiniMiranda = 0 THEN 'Mini-Miranda'          
END AS [Error Type] 
FROM Reviews INNER JOIN PCAs ON Reviews.PCAID = PCAs.PCAID LEFT JOIN 
PCARebuttal ON Reviews.ReviewID = PCARebuttal.ReviewID 
WHERE 
 (Score_CorrectID = 0 OR Score_ProperlyIdentified = 0 OR Score_MiniMiranda = 
0)

这会产生：

我想要这个：

标签： sqlsql-server-2012

Answer 1

使用apply：

SELECT r.ReviewID, v.error_type
FROM Reviews r INNER JOIN
     PCAs
     ON r.PCAID = PCAs.PCAID LEFT JOIN 
     PCARebuttal pr
     ON r.ReviewID = pr.ReviewID OUTER APPLY
     (SELECT *
      FROM (VALUES (Score_CorrectID, 'Correct ID Right Party Authentication'),  
                   (Score_ProperlyIdentified, 'PCA Properly Identified Itself'),
                   (Score_MiniMiranda, 'Mini-Miranda')
           ) v(score, error_type)
      WHERE score = 0
     ) v        
WHERE (Score_CorrectID = 0 OR Score_ProperlyIdentified = 0 OR Score_MiniMiranda = 0);

也就是说，我可能只是将这些值连接到一个列中：

SELECT r.ReviewID,
       ( (CASE WHEN Score_CorrectID = 0 THEN 'Correct ID Right Party Authentication; ' ELSE '' END) +  
         (CASE WHEN Score_ProperlyIdentified = 0 THEN 'PCA Properly Identified Itself; ' ELSE '' END) +
         (CASE WHEN Score_MiniMiranda = 0 THEN 'Mini-Miranda;' ELSE '' END)
       ) as error_types
FROM Reviews r INNER JOIN
     PCAs
     ON r.PCAID = PCAs.PCAID LEFT JOIN 
     PCARebuttal pr
     ON r.ReviewID = pr.ReviewID OUTER APPLY
     (SELECT *
      FROM (VALUES ()
           ) v(score, error_type)
      WHERE score = 0
     ) v        
WHERE (Score_CorrectID = 0 OR Score_ProperlyIdentified = 0 OR Score_MiniMiranda = 0);