SQL Query - Loop

Question

I'm trying to create a query that will generate a cross-validation table with about 40 custom columns that show Y or N. Right now I have

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]

So my question is: how do I create a loop (while? For?) That will cycle through the variable and assign a Y or N line based on the condition, rather than create 40+ Case statements?

Answer 1

You could not use a loop, but you could create a stored procedure / function to execute a subselect expression and an expression and call it 40 times.

In addition, you can improve sub-selection performance by changing it to

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

Answer 2

(.. ) , . 40 , , .

SQL Server . , , , . ,

CASE WHEN [Table1].[ID1] IN (SELECT ID2 FROM Table2 WHERE Variable = 2 AND Bit = 1)

CASE WHEN EXISTS (SELECT 0 FROM Table2 WHERE ID2 = [Table1].[ID1] AND Variable = 2 AND Bit = 1)

Answer 3

, , -. , SQL. SQL :

Select Company
    , Option1, Option2, Option3
    , Case When T2.Variable = 1 Then 'Y' Else 'N' End As CustomCol1
    , Case When T2.Variable = 2 Then 'Y' Else 'N' End As CustomCol2
    , Case When T2.Variable = 3 Then 'Y' Else 'N' End As CustomCol3
    , Case When T2.Variable = 4 Then 'Y' Else 'N' End As CustomCol4
... 
From Table1 As T1
        Left Join Table2 As T2
            On T2.ID2 = T1.ID
                And T2.Bit = 1
Where T1.OtherCondition = 'True'
Group By T1.Company
Order By T1.Company

-, Case ( SQL Server 2005+), - :

With Numbers As
    (
    Select 0 As Value
    Union All
    Select Value + 1
    From Numbers
    Where Value < 41
    )
Select ', Case When T2.Variable = ' + Cast(N.Value As varchar(10)) + ' Then ''Y'' Else ''N'' End As CustomCol' + Cast(N.Value As varchar(10))
From Numbers As N

.

Answer 4

One way could be to use the Pivot statement, which is located in MS SQL 2005+. But even that you have to put 1 ... 40 hard-coded columns in the summary instruction.

Another way I can think of is to create dynamic SQL, but this is not recommended, so we can create a dynamic SQL query by running the while loop on the table and creating a large sql, and then we can execute it using sp_execute. So the steps will be.

int @loopVar

SET @loopVar = 0

int @rowCount

varchar @SQL 

SET @SQl = ''
Select @rowcount = Count(ID2) from Table2

WHILE(@loopVar <= @rowCount)

BEGIN

// create ur SQL here

END

sp_execute(@SQL)