October 20, 2014 at 4:13 am
I have data in the following format
OrderID ItemName UnitPrice Quanity
1 car 10 2
2 Train 20 1
3 boat 30 4
4 plane 10 2
The lines are aggregated by quantity. I need to have a line for each item ordered. e.g. for orderID 1 i need to break this into 2 lines.
I can do this with cursors or a c# script in ssis but wondeirng if there is a better (more efficient) method either in SSIS or in TSQL?
October 20, 2014 at 4:55 am
winston Smith (10/20/2014)
I have data in the following format
OrderID ItemName UnitPrice Quanity
1 car 10 2
2 Train 20 1
3 boat 30 4
4 plane 10 2
The lines are aggregated by quantity. I need to have a line for each item ordered. e.g. for orderID 1 i need to break this into 2 lines.
I can do this with cursors or a c# script in ssis but wondeirng if there is a better (more efficient) method either in SSIS or in TSQL?
What would your two OrderId = 1 rows look like?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 20, 2014 at 5:15 am
OK, I think I worked it out. Do you mean something like this? By the way, please include sample DDL next time.
if object_id('tempdb..#Order', 'U') is not null
drop table #Order
create table #Order
(
OrderId int primary key ,
ItemName varchar(30) ,
UnitPrice int ,
Quantity int
)
insert #Order
( OrderId, ItemName, UnitPrice, Quantity )
values ( 1, 'Car', 10, 2 ) ,
( 2, 'Train', 20, 1 ) ,
( 3, 'Boat', 30, 4 ) ,
( 4, 'Plane', 10, 2 )
if object_id('tempdb..#Numbers', 'U') is not null
drop table #Numbers
create table #Numbers
(
Number int primary key
)
insert #Numbers
( Number
)
select top ( 1000 )
n = row_number() over ( order by [object_id] )
from sys.all_objects
order by n;
select o.OrderId ,
o.ItemName ,
o.UnitPrice
from #Order o
join #Numbers n on n.Number <= o.Quantity
order by o.OrderId ,
o.ItemName
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 20, 2014 at 5:16 am
You could use an inline tally table too:
CREATE TABLE #MyTable (OrderID INT IDENTITY(1,1), ItemName VARCHAR(20), UnitPrice INT, Quanity INT)
INSERT INTO #MyTable (ItemName, UnitPrice, Quanity)
VALUES ('car', 10, 2), ('Train', 20, 1), ('boat', 30, 4), ('plane', 10, 2);
WITH
e1 AS (SELECT * FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
e2 AS (SELECT a.n FROM e1 a, e1 b),
iTally AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM e2 a, e2 b)
SELECT *
FROM #MyTable
CROSS APPLY (SELECT TOP(Quanity) n FROM iTally) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply