Check Multiple Columns for Multiple values

[dhulbert]

I need to check 20 columns in th esame table for any 1 of 15 differnet codes.

I can obviously do it with a very messy nested casae statement but can anyone think of a neater/more efficient version?

Cheers

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[gmmastros]

I would probably write this as a union all query, like this:

Select *
From   (

       Select IdColumn, Col1 As SearchColumn From TableX
       Union All
       Select IdColumn, Col2 From TableX
       Union All
       Select IdColumn, Col3 From TableX
       Union All
       Select IdColumn, Col4 From TableX
       Union All
       etc....
       ) As Searches
Where  Searches.SearchColumn In ('x','y','z')

Or... if you have the different codes in a table...

Select *
From   (

       Select IdColumn, Col1 As SearchColumn From TableX
       Union All
       Select IdColumn, Col2 From TableX
       Union All
       Select IdColumn, Col3 From TableX
       Union All
       Select IdColumn, Col4 From TableX
       Union All
       etc....
       ) As Searches
       Inner Join CodesTable
         On Searches.SearchColumn = CodesTable.CodeValue

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom