locked
create multiple rows based on qty field RRS feed

  • Question

  • Hi guys,

    I have two table, the table1 contains data with Qty.
    In table2 need to get data from table1 based on SKU and defectcode as my reference.
    how could i make the result of my query looks like my sample result.

    Thank you in advance.

    Here is my sample data:

    Create table #table1
    (SKU nvarchar(35), PONum nvarchar(10), defectcode int, qty int, price numeric(28,12), transprice numeric(28,12))
    Insert into #table1 values('P245-3110-ESTEEM-U','11566377',331,1.00,298.99,208.75)
    Insert into #table1 values('P300-1110-INCREDIBLE-U','11566303',505,2.00,310.00,131.38)
    Insert into #table1 values('P300-1110-INCREDIBLE-U','11566303',505,1.00,310.00,131.38)
    Insert into #table1 values('P300-1110-INCREDIBLE-U','11566303',524,1.00,310.00,131.38)
    Insert into #table1 values('P300-1110-INCREDIBLE-U','11466194',505,2.00,310.00,106.06)
    Insert into #table1 values('P300-1110-INCREDIBLE-U','11361093',533,4.00,310.00,102.49)
    Create table #table2
    (SKU nvarchar(35), defectcode int, ESN nvarchar(35))
    Insert into #table2 values('P245-3110-ESTEEM-U',331,'99000060207759')
    Insert into #table2 values('P300-1110-INCREDIBLE-U',505,'270113179908936788')
    Insert into #table2 values('P300-1110-INCREDIBLE-U',505,'270113179508255690')
    Insert into #table2 values('P300-1110-INCREDIBLE-U',505,'270113179911473223')
    Insert into #table2 values('P300-1110-INCREDIBLE-U',524,'270113179505524006')
    Insert into #table2 values('P300-1110-INCREDIBLE-U',505,'270113179508220437')
    Insert into #table2 values('P300-1110-INCREDIBLE-U',505,'270113179908449411')
    Insert into #table2 values('P300-1110-INCREDIBLE-U',533,'270113179508284013')
    Insert into #table2 values('P300-1110-INCREDIBLE-U',533,'270113179908924246')
    Insert into #table2 values('P300-1110-INCREDIBLE-U',533,'270113179908953248')
    Insert into #table2 values('P300-1110-INCREDIBLE-U',533,'270113179911550981')
    EXPECTED RESULT:
    SKU-------------------DEFECTCODE--------ESN----------QTY---PONUM------PRICE----TPRICE
    P245-3110-ESTEEM-U-------331----99000060207759-------1-----11566377---298.99---208.75
    P300-1110-INCREDIBLE-U---505----270113179908936788---1-----11566303---310.00---131.38
    P300-1110-INCREDIBLE-U---505----270113179508255690---1-----11566303---310.00---131.38
    P300-1110-INCREDIBLE-U---505----270113179911473223---1-----11566303---310.00---131.38
    P300-1110-INCREDIBLE-U---524----270113179505524006---1-----11566303---310.00---131.38
    P300-1110-INCREDIBLE-U---505----270113179508220437---1-----11466194---310.00---106.06
    P300-1110-INCREDIBLE-U---505----270113179908449411---1-----11466194---310.00---106.06
    P300-1110-INCREDIBLE-U---533----270113179508284013---1-----11361093---310.00---102.49
    P300-1110-INCREDIBLE-U---533----270113179908924246---1-----11361093---310.00---102.49
    P300-1110-INCREDIBLE-U---533----270113179908953248---1-----11361093---310.00---102.49
    P300-1110-INCREDIBLE-U---533----270113179911550981---1-----11361093---310.00---102.49

    Wednesday, November 14, 2012 7:57 AM

Answers

  • You need a numbers table. Once you have it, this query becomes very simple:

    select t1.sku,t1.defectcode,esn,1 AS qty,ponum,price
    from #table2 t1  join #table1 t2
    on t2.defectcode=t1.defectcode
    and t2.sku=t1.sku
    INNER JOIN dbo.Numbers N ON N.Number between 1 and t2.qty
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, November 15, 2012 2:50 AM

All replies

  • Hi,

    Can you explain by what criteria got the above result.

    Thanks

    Wednesday, November 14, 2012 9:27 AM
  • Here is my inital query. any suggestion about my query. thanks.

    ;With cte as
    (
    select t1.SKU, t1.PONum, t1.defectcode, t1.qty, t1.price, t1.transprice, t2.rqty
    from #table1 t1
    Cross apply (Select SUM(qty) as rqty from #table1
    			Where SKU=t1.SKU
    			and defectcode=t1.defectcode) t2	
       		    --and PONum=t1.PONum
    )
    Select *, 1 as returnqty
    from (Select ROW_NUMBER() over (partition by sku, defectcode order by esn) as seq, * from #table2) p			
    Cross apply (select top 1 SKU, ponum, defectcode, qty, price, transprice
                 from cte where SKU=p.SKU and defectcode=p.defectcode 
                 and rqty >=p.seq order by rqty) x


    • Edited by Lenoj Wednesday, November 14, 2012 9:43 AM add
    Wednesday, November 14, 2012 9:43 AM
  • What is logic behind the scene?

    select t1.sku,t1.defectcode,esn,qty,ponum,price
    from #table2 t1  join #table1 t2
    on t2.defectcode=t1.defectcode
    and t2.sku=t1.sku


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Wednesday, November 14, 2012 9:48 AM
    Answerer
  • Try the below one

    Select t2.SKU,t2.defectcode,t2.ESN,t1.qty,t1.PONum,t1.price from #table2 t2
    Inner join #table1 t1 on t2.SKU = t1.SKU where t1.qty = 1


    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Wednesday, November 14, 2012 9:53 AM
  • Hi,

    Try the below Query

    select t1.sku,t1.defectcode,esn,CASE WHEN qty > 1 THEN 1 ELSE qty END,ponum,price,transprice from #table2 t1 join #table1 t2 on t2.defectcode=t1.defectcode and t2.sku=t1.sku



    PS.Shakeer Hussain

    Wednesday, November 14, 2012 10:07 AM
  • the problem with my script, i did not get the exact records. the related records based on SKU and FAILCODE.

    here is another sample:

    My Objective is to relate both TABLE
    my data source is #TABLE2 and my look up table is #TABLE1.
    Then I need to use the SKU and defectcode from #TABLE2 as my reference to get the specific records in #TABLE1.
    then get the price, transprice and ponum and replicate those records to create columns.

    the qty (4) from #TABLE1 is the summarize per SKU and failcode, while #TABLE2 data is the details (Expand) based on Qty

     #TABLE1
    ex. SKU-------------------QTY------PONUM--+----DEFECTCODE----PRICE----TRANSPRICE
    P300-1110-INCREDIBLE-U-----4------11361093---------533--------310-------102.49
    #TABLE2
    ------SKU--------------DEFECTCODE---------ESN----------
    P300-1110-INCREDIBLE-U--533----------270113179508284013
    P300-1110-INCREDIBLE-U--533----------270113179908924246
    P300-1110-INCREDIBLE-U--533----------270113179908953248
    P300-1110-INCREDIBLE-U--533----------270113179911550981
    EXPCTED RESULT:
    ------SKU--------------DEFECTCODE---------ESN----------------PONUM ---PRICE----TRANSPRICE
    P300-1110-INCREDIBLE-U--533----------270113179508284013----11361093---310-------102.49
    P300-1110-INCREDIBLE-U--533----------270113179908924246----11361093---310-------102.49
    P300-1110-INCREDIBLE-U--533----------270113179908953248----11361093---310-------102.49
    P300-1110-INCREDIBLE-U--533----------270113179911550981----11361093---310-------102.49

    Thursday, November 15, 2012 2:32 AM
  • You need a numbers table. Once you have it, this query becomes very simple:

    select t1.sku,t1.defectcode,esn,1 AS qty,ponum,price
    from #table2 t1  join #table1 t2
    on t2.defectcode=t1.defectcode
    and t2.sku=t1.sku
    INNER JOIN dbo.Numbers N ON N.Number between 1 and t2.qty
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, November 15, 2012 2:50 AM
  • THank you Naomi. I will try this.
    • Marked as answer by Lenoj Thursday, November 15, 2012 6:20 AM
    • Unmarked as answer by Lenoj Thursday, November 15, 2012 6:20 AM
    Thursday, November 15, 2012 6:20 AM