Solved

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

Posted on 2012-04-09
8
826 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
Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

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