Solved

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

Posted on 2012-04-09
8
821 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
8 Comments
 
LVL 39

Expert Comment

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

Author Comment

by:cjtumlin
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now