adonetok (5/16/2013)
In fact, in real table there are about 20 colums and 200 ID need to check cell by cell.I do not think that without loop can do it.
Can someone help me?
20 columns for this is not that big of a deal. You can code that solution in about 5 - 10 minutes. A loop should be your absolute LAST resort. If you do this in a loop you still have to evaluate all the columns so you aren't saving development time or keystrokes. It is more complicated and far slower.
To be honest from your description it sounds like you could stand to do some normalization on your tables. Are all 20 columns mutually exclusive like you posted? in other words can only 1 of those 20 columns be checked? If so, then you really need to look at normalizing that. You are using 20 columns to hold a single piece of information.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/