Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2008
    Posts
    48

    Unanswered: Replicating row by value in column

    I have following data :

    Table1 :

    Order Count Line_No Qty
    100 4 Line1 10
    100 3 Line2 20
    101 2 Line1 10
    101 1 Line2 20

    I want following output Count column tell how many times row is to be replicated
    sno Order Line_No Qty
    1 100 Line1 10
    2 100 Line1 10
    3 100 Line1 10
    4 100 Line1 10

    1 100 Line2 20
    2 100 Line2 20
    3 100 Line2 20

    1 101 Line1 10
    2 101 Line1 10

    1 101 Line2 20

    Can I use recursive or CONNECT BY to get this output. Thanks in advance

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Try this:

    Code:
    with t (sno, Order, Line_No, Qty) as (
    select Count, Order, Line_No, Qty
    from table(values 
      (100, 4, 'Line1', 10)
    , (100, 3, 'Line2', 20)
    , (101, 2, 'Line1', 10)
    , (101, 1, 'Line2', 20)
    ) table1 (Order, Count, Line_No, Qty)
      union all
    select sno-1, Order, Line_No, Qty
    from t
    where sno>1
    )
    select *
    from t
    order by Order, Line_No, sno
    Regards,
    Mark.

  3. #3
    Join Date
    Nov 2008
    Posts
    48
    Thank you Mark.

Posting Permissions

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