Searching for value across multiple columns 1

[ousoonerjoe]

Using SQL Server 2008.

We have some legacy accounting tables that contain Amt1, Amt2, Amt3,...Amt15. We need to search for a value across all 15 fields. Besides typing out all 15 variations, is there a simpler way to return all rows that contain the given value in any column of the table?


Thank you.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[markros]

There is a blog post answering this question

http://blogs.lessthandot.com/index..../the-ten-most-asked-sql-server-questions--1#2

You just simplify the logic from this blog a little bit.

PluralSight Learning Library

 

[markros]

Also here is the alternative solution for your case

;with cteNormData as (select * from
myTable
UNPIVOT (Amt for AMOUNT in ([Amt1],[Amt2],..[Amt15])

select * from cteNormData where Amt = @Amount

See

http://msdn.microsoft.com/en-us/library/ms177410.aspx

PluralSight Learning Library

 

[ousoonerjoe]

Thank you, Markros. That's exactly what i looking for.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------