de-aggregate or Un-aggregate rows. whats best method?

  • 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?

  • 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

  • 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

  • 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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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