Flatten a one to many?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2005 » T-SQL (SS2K5) » Flatten a one to many?

Add to briefcase

13 posts, Page 1 of 2

1 2 »»

Flatten a one to many?

Expand / Collapse

Author

Message

Posted Thursday, March 07, 2013 1:55 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 08, 2013 1:52 PM
Points: 29, Visits: 86

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

Post #1428253

Posted Thursday, March 07, 2013 2:22 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 3:11 PM
Points: 8,938, Visits: 8,512

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1428267

Posted Thursday, March 07, 2013 3:09 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 08, 2013 1:52 PM
Points: 29, Visits: 86

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.

Post #1428286

Posted Thursday, March 07, 2013 3:27 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 3:11 PM
Points: 8,938, Visits: 8,512

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1428295

Posted Thursday, March 07, 2013 3:30 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 08, 2013 1:52 PM
Points: 29, Visits: 86

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

Post #1428301

Posted Thursday, March 07, 2013 4:39 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:35 PM
Points: 33,107, Visits: 27,029

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1428323

Posted Friday, March 08, 2013 8:24 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 08, 2013 1:52 PM
Points: 29, Visits: 86

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

Post #1428603

Posted Friday, March 08, 2013 8:52 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 3:11 PM
Points: 8,938, Visits: 8,512

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1428619

Posted Friday, March 08, 2013 9:30 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:44 PM
Points: 6,826, Visits: 11,948

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1428649

Posted Friday, March 08, 2013 9:34 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 08, 2013 1:52 PM
Points: 29, Visits: 86

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

Post #1428653

« Prev Topic | Next Topic »

Add to briefcase

13 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse