Pull two Fields into a single field, but not Concatenate in SQL

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?
Who is Participating?
Patrick MatthewsCommented:
I agree with als315 that this should be normalized.  In the short term, you could use:


SELECT Product_Code, Ingredient_Code_1 AS Ingredient_Code
FROM tbl_Ingredients
WHERE Ingredient_Code_1 IS NOT NULL
SELECT Product_Code, Ingredient_Code_2 AS Ingredient_Code
FROM tbl_Ingredients
WHERE Ingredient_Code_2 IS NOT NULL
SELECT Product_Code, Ingredient_Code_3 AS Ingredient_Code
FROM tbl_Ingredients
WHERE Ingredient_Code_3 IS NOT NULL
<you get the idea>
SELECT Product_Code, Ingredient_Code_20 AS Ingredient_Code
FROM tbl_Ingredients
WHERE Ingredient_Code_20 IS NOT NULL
You should normalize this table. Create new one with necessary columns and 20 queries will add data to it.
cjtumlinAuthor Commented:
I don't have the access to do that in the database itself. If I were to try it in SQL, would that work?
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!


You're working with a many to many relationship, and, since a table only has rows and columns (2 dimensions), you'll need to either create and inbetween location or perform a concatenation (assuming you aren't ready to overhaul the existing structure).  

The inbetween location (table or view) could house a row for each Product Code and Ingerdient Code.  As such, a product with 10 ingredients would have 10 records - each of which has the same product code and a different ingredient code.  

PCd#1    IngCd#1    IngCd#2    IngCd#3
PCd#2    IngCd#1    IngCd#2    IngCd#3

PCd#1    IngCd#1
PCd#1    IngCd#2
PCd#1    IngCd#3
PCd#2    IngCd#1
PCd#2    IngCd#2
PCd#2    IngCd#3

This is a denormalizing process.

You can also add the name of the ingredient to this location:

PCd#1    IngCd#1    IngNM#1
PCd#1    IngCd#2    IngNM#2
PCd#1    IngCd#3    IngNM#3
PCd#2    IngCd#1    IngNM#1
PCd#2    IngCd#2    IngNM#2
PCd#2    IngCd#3    IngNM#3

This table / view is quite searchable, and indexable.  You're SELECTS will need to take the structure into account, and you've a prime example of where to use grouping (ProdCd or IngCd).
For key relation ships, use either a compisite primary key on PCd and IngCd, or create a new field that concatenates the two together and use it as the primary. (assumes one ingerdient cannot be used multiple times in the same Product)

Does that sould like what you were looking for?

I didn't see that you do not have DB access.  That's going to be importent for any denormalization to a table or view.  From the Admin comment, I take it this is for Crystal Reports.  The view would need to be presented to Crystal R from the data source.  Are you using Busines Objects to intoface with SQL Server, or straigt through to SQL Server with an ODBC / OLEDB / JDBC connection?

cjtumlinAuthor Commented:
I'm pulling data using ODBC from the Cimpro database into a SQL database from which we can then create Crystal reports.  We cannot have Crystal pull directly from Cimpro as it crashes constantly and brings production to a halt.

I'm trying the suggestion from matthewspatrick at the moment to see if I can get what I need.  Since I'll have to use the UNION clause 20 times to get everything pulled in, I'm taking it chunks and hoping It will cooperate.  I'll let you all know.
cjtumlinAuthor Commented:
This worked!  I was able to create a SQL view and write a crystal report using that linked with other tables in Cimpro to get the ingredient usage information we needed without all the drama of using the original table.  Thank you so much!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.