July 12, 2012 at 10:03 am
Let me start out by saying Yuck :sick:, but I have to ask this question:
I am creating a new SSIS package that takes orders and sends them to our manufacturing facility. The ordering schema/data model is what you would think: Order header and Order Items with a quantity column. Unfortunately, the software on the manufacturing side can only deal with a quantity of 1, so I need to create <quantity> rows as input to manufacturing for each row in the OrderItems table.
Cursor? Yuck. Is there a transform in SSIS that can be twisted to do this? Or is there some sneaky SQL way to do it?
Any other ideas?
Historical versions of the ordering database/ui actually created <quantity> rows in the the order database, and the UI displayed COUNT(DISTINCT ItemId) as the quantity. We are in the process of building a new database, and don't want to pollute the schema with this same practice.
create table #OrderItems (OrderId int,
ProductId int,
Quantity int)
insert #OrderItems (OrderId, ProductId, Quantity)
Values (1, 1, 3)
Desired output: 3 rows:
1 1
1 1
1 1
July 12, 2012 at 10:45 am
You can do this pretty easily with a tally table.
select OrderId, ProductId, 1
from #OrderItems o
join Tally t on t.N <= o.Quantity
You can read about a tally table here. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 12, 2012 at 10:50 am
Of course. Duh! I've gotten dull due to heat or old age (or both).
Thanks.
July 12, 2012 at 10:52 am
LOL we have all been there. 😉 Glad that helped.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply