SQL Server query - loop question

Question

I'm trying to create a query that would generate a cross-check 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 loop on variable and assign Y or N to the row based on the condition, rather than creating 40+ Case statements?

Answer 1

You couldn't use a loop, but you could create a stored procedure/function to perform the sub-select and case expression and call that 40 times.

Also, you could improve performance of the sub-select by changing it to

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

Answer 2

One way could have been to use Pivot statement, which is in MS SQL 2005+. But even in that you have to put 1 ... 40 hardcoded columns in pivot statement.

Other way i can think of is to create dynamic SQL, but it is not so much recommended, So what we can do is we can create a dynamic sql query by running a while loop on table and can create the big sql and then we can execute it by using sp_execute. So steps would 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)

Answer 3

A loop (that is, iterating through a cursor) works on rows, not columns. You will still have to have 40 expressions, one for each column, and the performance will be terrible.

Let SQL Server do its job. And do your bit by telling exactly what you need and creating proper indices. That is, replace

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

with

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

Answer 4

If the output is so vastly different than the schema, there is a question as to whether the schema properly models the business requirements. That said, I would recommend just writing the SQL. You can simplify the SQL like so:

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

If you want to write something that can help you auto-gen those Case statements (and you are using SQL Server 2005+), you could do something like:

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

You would run the query and copy and paste the results into your procedure or code.