Search through multiple columns and find first occurence of value from a list
Posted on 2007-10-15
I am in the process of doing some field mapping, and the first step is to clean up some existing data. Here is what I need to accomplish
table 1 -
UniqueID Category1 Category2 Category3 Category4
123 100 200 300 400
For each uniqueID in table1, I need to search through each category column, and find the first categoryID that is in table2.
Based on the example above, I would expect that ID 123 would return 300.
I was thinking of creating a udf with each category column as a parameter. It would evaluate the 1st column..does it exist in table2. Yes - return category and exit function. No - move on to the next column. If nothing was found return a deafault value of -1.
I wanted to see if there was an easier, or more efficient way of doing this.