Complex query with multiple normalized fields
Not many people like them, but I would suggest you use a cursor in a stored procedure to loop though the fields and normalise them into the target table (silly ETL process).  

You could probably do better having had separate lookup tables for each type of field, but it depends on you scenario.