SQL Server查询-循环问题

Question

我正在尝试创建一个查询，该查询将生成一个交叉检查表，其中包含大约 40 个显示 Y 或 N 的自定义列。现在我有

SELECT DISTINCT [Company],
       [Option1],
       [Option2],
       [Option3],              
       CASE
         WHEN [Table1].[ID1] IN (SELECT ID2 FROM Table2 WHERE Variable = 1 AND Bit = 1) THEN 
           'Y'
         ELSE 'N'
       END AS 'CustomColumn1:',
       CASE 
         WHEN [Table1].[ID1] IN (SELECT ID2 FROM Table2 WHERE Variable = 2 AND Bit = 1) THEN 
           'Y'
         ELSE 'N'
       END AS 'CustomColumn1:',      
       CASE 
         WHEN [Table1].[ID1] IN (SELECT ID2 FROM Table2 WHERE Variable = 3 AND Bit = 1) THEN 
           'Y'
         ELSE 'N'
    END AS 'CustomColumn1:',
    .............
    -- REPEAT ANOTHER 40 times
    FROM [Table1] 
    WHERE [Table1].[OtherCondition] = 'True'
    ORDER BY [Company]

所以我的问题是，如何创建一个循环(while？for？)来循环变量并根据条件将 Y 或 N 分配给行，而不是创建 40 多个 Case 语句？

Answer 1

你不能使用循环，但你可以创建一个存储过程/函数来执行子选择和 case 表达式并调用它 40 次。

此外，您可以通过将其更改为来提高子选择的性能

SELECT 1 FROM Table2 WHERE EXISTS [Table2].[ID2] = [Table1.ID1] AND Variable = 3 AND Bit = 1