OK I can use the UNPIVOT to get me the information of the mf_# that contains a matching value.

select pkey, mf_value, mf_field from table1

unpivot

(mf_value for mf_field in (mf_1, mf_2, mf_3,..., mf_50)) as pvtTable where mf_value = 'ABC'

Now my returned rows are...

1, 'ABC',mf_1

2,'ABC',mf_4

and this is good, how would i go about using mf_field value mf_1 and mf_4 to reference a corresponding field in another table.

i would like get the values for cf_1 and cf_4 to go along with the results above...

Table2

pkey,

cf_1 int,

cf_2 int,

cf_3 int,

cf_4 int,

...

cf_50;