Results 1 to 3 of 3

Thread: TSQL Question

  1. #1
    Join Date
    Oct 2003
    Posts
    20

    Unhappy Unanswered: TSQL Question

    Is there a SQL that could explode the 1st result set into the 2nd result set without using
    cursor processing?


    acctnbr total payments (1st result set)

    237655643 3
    217384950 2
    243910349 4
    224739507 n


    CONVERTED INTO:


    acctnbr payment number (2nd result set)

    237655643 1
    237655643 2
    237655643 3
    217384950 1
    217384950 2
    243910349 1
    243910349 2
    243910349 3
    243910349 4
    224739507 1
    ......
    224739507 n

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    A Solution, but not great

    Hi,

    I have a solution, but it involves building a table with as many rows as the largest number of total payments. It's a task that only has to be done once, but I can't think of anything that allows you to un-aggregate data once it's been aggregated.

    The following script performs a cartesian product with the row_seed table to generate the number of rows required. The exact number of rows is determined by the "total_payments" column.

    The table agg_data, stores your aggregate data (account number and total payments), as supplied in your original post.

    The final select state generates the output as required. The physical order of the rows is only determined by the physical order in agg_data. You'll probably want to order by account number and row id to make sense of the output.

    -- Create and populate agg_data with the values from the original post

    create table agg_data ( account_number char(9), total_payments int)
    go
    insert into agg_data values ('237655643', 3)
    insert into agg_data values ('217384950', 2)
    insert into agg_data values ('243910349', 4)
    go


    -- Create and populate row_seed

    create table row_seed ( row_id numeric(10,0))
    go


    -- Populate row_seed with 1000 entries. I real life I expect this to be a much larger number.

    declare
    @count int,
    @max_rows_required int

    select
    @count = 1,
    @max_rows_required = 1000

    while @count <= @max_rows_required
    begin
    insert row_seed (row_id) select @count

    select
    @count = @count + 1
    end
    go


    -- This query performs a cartesian product between agg_data and row_seed generating (total_payment) rows for each account number. I've included the optional order by.

    select
    a.account_number,
    r.row_id
    from
    agg_data a,
    row_seed r
    where
    r.row_id <= a.total_payments
    order by
    a.account_number,
    r.row_id

  3. #3
    Join Date
    Oct 2003
    Posts
    20
    Thank you, Richard. I will give it a shot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •