SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Flatten a one to many?


Flatten a one to many?

Author
Message
kpwimberger
kpwimberger
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 128
Hello all. I’ve got an issue with flattening a 1 to many relationship. For argument’s sake, let’s say I have a database that keeps track of pencils manufactured. Pencils are manufactured in lots, so two lots of pencils will not have the same lot Id.

Once a lot comes off the line the pencils are stored for later distribution. The pencils may not all be stored in one location, however. It’s possible that out of a lot of 500 pencils 200 may be stored in cabinet A, shelf 2; and another 300 may be stored in cabinet F, shelf 1.

But don’t order yet! Because the legacy system didn’t record how many individual pencils from this lot are stored in each location - only the fact that some pencils in this lot are stored across many possible locations – the new system also has this horrible constraint. (And before you tell me this is poor design I agree wholeheartedly, but the client refuses to re-inventory 70,000 pencils to record their exact location, so this is not an option.)

So now saleswoman Brenda wants to pull pencils from lot Id ABC123. She searches the system and sees that lot ABC123 has 750 pencils left in stock. She places an internal order for 400 of those pencils. The system says “can do!” and places her order. It then reports the fact that of the 400 pencils she ordered of this lot, she will have to pull from the following locations – sorry, but we can’t be any more specific Brenda!

Pencils of lot ABC123 can be found in:
Cabinet A, shelf 1
Cabinet D, shelf 1
Cabinet D, shelf 2
Cabinet K, shelf 2

When it comes time to print out Brenda’s order I had been using a user-defined function to concatenate together a list of all potential storage locations so that we kept a one-line per lot Id and qty relationship. Here order would look a bit like this:

Lot Id || QTY || LOCATIONS
------------------------------------------------------------------
ABC123 || 400 || Cabinet A, shelf 1; Cabinet D, shelf 1; Cabinet D, shelf 2; Cabinet K, shelf 2

A workaround, yes – but it did work. But wait, there’s more! Now it has been decided that some of the pencils are of such a sophisticated design that foreign powers would want to take them from the factory. The solution is to encrypt all of the storage location data so no one can actually know where the pencils are stored until the very end of their order. Big problem though – you cannot open or close a symmetric key inside a UDF so now the ability to concatenate all possible storage locations into one line of the order is lost.

But if you actually JOIN to the storage locations table you wind up duplicating order lines; one line for each storage location. And this in turn then shows false quantities ordered which in turn then sum incorrectly at the footer of the order. Hooray!

So, does anyone have a suggested workaround that returns one line of multiple storage locations for each lot ordered?

Thanks. Kurt
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26526 Visits: 17557
From what you are describing it sounds like what you have is over complicated for the results. It is however pretty much impossible to offer any kind of direct help because there are not enough details to work with. If you want specific coding help please take a few minutes to read the first link in my signature for best practices when posting questions.

You might also take a look at this article http://www.sqlservercentral.com/articles/comma+separated+list/71700/. It explains how to generate a comma separated list of rows which I think is basically what you are trying to do here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
kpwimberger
kpwimberger
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 128
Hello Sean. Apologies, I will try to clarify.

I have a pencils table that describes the pencils.
pkPencilId || PencilDescription || LENGTH
1 || Yellow, w/o erase || 5”
2 || Yellow, w/eraser || 5”
3 || Green, w/o eraser || 6”

Then there is a PencilLot table.
pkLotId || flPencilId || Created || QTY
100 || 2 || 11/15/2007 || 1000
200 || 1 || 12/21/2008 || 800
300 || 3 || 4/22/2011 || 2000
400 || 1 || 5/12/2011 || 900

Then we have a locations table that keeps track of what lots are spread over what locations.
pkStorageId || fkLotId || LOCATION
5 || 200 || Cabinet A, shelf 1
6 || 200 || Cabinet B, shelf 2
7 || 200 || Cabinet K, shelf 2
8 || 100 || Cabinet B, shelf 3
9 || 300 || Cabinet D, shelf 2
10 || 300 || Cabinet D, shelf 3

So if my saleswoman Brenda wants to pull 750 pencils made in lot #300, her order might look like this:

Sales Person: Brenda
PencilType || LotId || Location || Qty Requested
3 || 300 || Cabinet D, shelf 2 || 750
3 || 300 || Cabinet D, shelf 3 || 750
Total for order: 1,500

But of course she did not order TWO sets of 750 pencils. These line items are split into two because of the one --> many relationship between lotId and storage locations. The order SHOULD read:

Sales Person: Brenda
PencilType || LotId || Location || Qty Requested
3 || 300 || Cabinet D, shelf 2, Cabinet D, shelf 3 || 750
Total for order 750

Further compounding the issue is that she can place more than 1 lotId on a single order. I think the example you steered me towards may be it, but I need to see if that example will work for multiple different lotIds on one order. Hope this explains a bit better.

Thanks.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26526 Visits: 17557
kpwimberger (3/7/2013)
Hello Sean. Apologies, I will try to clarify.


It would be better if you had read the article. The problem is it is really hard to visualize your tables and it is even harder to create queries when there are no tables. If you want actual coding assistance you need to provide ddl, sample data and desired output.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
kpwimberger
kpwimberger
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 128
I have been reading the article and am trying to apply that fix now. I thought the additional data I sent would have helped. I may be able to simplify actual structures and post query code, but that will take some time as the example I gace was obviously not what Iam actually working on.

I will see if I can whip something up if my attempts to apply the article fix fail.

Thanks.

Kurt
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88438 Visits: 41128
kpwimberger (3/7/2013)
I have been reading the article and am trying to apply that fix now. I thought the additional data I sent would have helped. I may be able to simplify actual structures and post query code, but that will take some time as the example I gace was obviously not what Iam actually working on.

I will see if I can whip something up if my attempts to apply the article fix fail.

Thanks.

Kurt


It doesn't have to be the actual data. Just post the data you previously posted in a readily consumable format like in the article.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
kpwimberger
kpwimberger
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 128
Thanks Jeff and Sean. The fix Sean steered me to to (using XML PATH and STUFF in a corelated query) worked! Now I just need to pull it all apart a few times to see if I can understand all the parts. I hate applying a fix I don't fully understand.

Thanks for the help.

Kurt
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26526 Visits: 17557
kpwimberger (3/8/2013)
Thanks Jeff and Sean. The fix Sean steered me to to (using XML PATH and STUFF in a corelated query) worked! Now I just need to pull it all apart a few times to see if I can understand all the parts. I hate applying a fix I don't fully understand.

Thanks for the help.

Kurt


You are welcome. Glad you were able to figure it out.

+10000 to wanting to understand the code and not liking using code you don't understand.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15127 Visits: 14396
kpwimberger (3/8/2013)
Now I just need to pull it all apart a few times to see if I can understand all the parts. I hate applying a fix I don't fully understand.

That's refreshing!

Happy you found a fix!

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
kpwimberger
kpwimberger
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 128
Thanks Sean and OPC:

I figure it just bites you on the.. uh, ankle... later on if you don't follow what was given. I had no idea that FOR XML could concatenate columns and customize output. I actually love learning new things so it all works out in the end.

Have a great day.

Kurt
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search