?
Solved

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

Posted on 2012-04-09
8
Medium Priority
?
833 Views
Last Modified: 2012-04-09
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?
0
Comment
Question by:cjtumlin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 40

Expert Comment

by:als315
ID: 37824297
You should normalize this table. Create new one with necessary columns and 20 queries will add data to it.
0
 

Author Comment

by:cjtumlin
ID: 37824349
I don't have the access to do that in the database itself. If I were to try it in SQL, would that work?
0
 
LVL 3

Expert Comment

by:Chris__W
ID: 37824404
Hello,

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

becomes
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?

Thanks,
Chris
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 37824424
I agree with als315 that this should be normalized.  In the short term, you could use:


CREATE VIEW dbo.MyView AS

SELECT Product_Code, Ingredient_Code_1 AS Ingredient_Code
FROM tbl_Ingredients
WHERE Ingredient_Code_1 IS NOT NULL
UNION ALL
SELECT Product_Code, Ingredient_Code_2 AS Ingredient_Code
FROM tbl_Ingredients
WHERE Ingredient_Code_2 IS NOT NULL
UNION ALL
SELECT Product_Code, Ingredient_Code_3 AS Ingredient_Code
FROM tbl_Ingredients
WHERE Ingredient_Code_3 IS NOT NULL
UNION ALL
<you get the idea>
UNION ALL
SELECT Product_Code, Ingredient_Code_20 AS Ingredient_Code
FROM tbl_Ingredients
WHERE Ingredient_Code_20 IS NOT NULL
0
 
LVL 3

Expert Comment

by:Chris__W
ID: 37824425
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?

Thanks,
Chris
0
 

Author Comment

by:cjtumlin
ID: 37824515
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.
0
 

Author Closing Comment

by:cjtumlin
ID: 37824966
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!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this article, we’ll look at how to deploy ProxySQL.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question