We have an ancient database that uses a strange system of listing ingredients for products. Each product code can have up to 20 ingredients, but each of those ingredients has it's own field in the table. So insead of having a field for Product_Code, a field for Ingredient_Code and a field for Ingredient_Number, we have a field for Product_Code, a field for Ingredient_Code_1, a field for Ingredient_Code_2, a field for Ingredient_Code_3, etc. There are additional 20 fields for the unit of measure associate with each ingredient, and another 20 fields for the quantity of each ingredient needed. What this returns is one single row of data for each product code.
To make things even worse, the ingredient type is not stored in this table. Therefore to identify if an ingredient is titanium oxide, or calcium, or whatever else we have to link each of these 20 fields to the table that holds that information individually and create a search that looks at all 20 linked type fields. The problems grow from there.
Needless to say this makes creating a report that searches for any product that has a particular ingredient type very difficult.
I need to know if there is a way in SQL to combine BUT NOT CONCATENATE these individual ingredient fields into a single searchable column in a view or table. I need to end up with all ingredients in one searchable field that can then be linked to the table which holds the type information. I don't need to keep the individual ingredient numbers at all. I just need one column that holds all ingredient codes and one column holding the item code.
Is this even possible?