actually i am looking more for something like this

select pkey, mf_value, mf_field, REPLACE(mf_field,'mf_','cf_') as cf_field from table1, table2

unpivot

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

Yeah i know that isn't the way to do it but that is what i am looking for...