How to turning 1 Row into 2 Rows following best practices?

  • Hello,

    I am looking for advice to write code following best practices.  I will be receiving a data file that contains Time and Expenses as individual columns in one row.  However, the system I need to import into requires (2) individual rows for each Time and Expense.  What is the best way to turn one row into two rows?

    The data I will be receiving data from provider might look like this:

    -- DROP TABLE #p
    CREATE TABLE #P (CustNo int, TimeAmount money, ExpenseAmount money)
    INSERT INTO #P (CustNo, TimeAmount, ExpenseAmount) VALUES (1, 100, 50)
    INSERT INTO #P (CustNo, TimeAmount, ExpenseAmount) VALUES (2, 0, 500)
    INSERT INTO #P (CustNo, TimeAmount, ExpenseAmount) VALUES (3, 250, 0)
    INSERT INTO #P (CustNo, TimeAmount, ExpenseAmount) VALUES (4, 1000, 500)
    INSERT INTO #P (CustNo, TimeAmount, ExpenseAmount) VALUES (5, 1250, 100)
    -- SELECT * FROM #P

    The table I want to import data into looks like this:

    -- AmountType 1 = Time, 2=Expense
    -- DROP TABLE #T
    CREATE TABLE #T (CustNo int, AmountType int, Amount money)
    INSERT INTO #T (CustNo, AmountType, Amount) VALUES (1, 1, 100)
    INSERT INTO #T (CustNo, AmountType, Amount) VALUES (1, 2, 50)
    INSERT INTO #T (CustNo, AmountType, Amount) VALUES (2, 2, 500)
    INSERT INTO #T (CustNo, AmountType, Amount) VALUES (3, 1, 250)
    INSERT INTO #T (CustNo, AmountType, Amount) VALUES (4, 1, 1000)
    INSERT INTO #T (CustNo, AmountType, Amount) VALUES (4, 2, 500)
    INSERT INTO #T (CustNo, AmountType, Amount) VALUES (5, 1, 1250)
    INSERT INTO #T (CustNo, AmountType, Amount) VALUES (5, 2, 100)
    -- SELECT * FROM #T

    What is the best way to convert the data from the provider #p table into my database table #t?

    Many thanks in advance!

    • This topic was modified 1 year, 1 month ago by  rjjh78.
  • Something like this?

    SELECT CustNo, TimeAmount AS Amount, 1 AS ExpenseType FROM #P
    UNION ALL
    SELECT CustNo, ExpenseAmount, 2 FROM #P;
  • If you don't want to write the data twice and you're using SSIS then you could multicast the dataflow to two destinations that are the same table. After the multicast you would derive the AmountType column and then insert the CustNo, AmountType and the appropriate Amount column. Inserting into a two column table and then unioning the amounts is easier. You either write the data twice or duplicate it in memory so it may depend on the size of the file, or personal preference

  • simple outer apply is the better option I think

    select p.CustNo
    , amt.ExpenseType
    , amt.Amount
    from #p p
    outer apply (select 1 as ExpenseType, TimeAmount as Amount
    union all
    select 2 as ExpenseType, ExpenseAmount as Amount
    ) amt
  • Instead of making 2 passes through the table, use a CROSS APPLY to do the "unpivot".  There IS an UNPIVOT operator but I don't care for it.

     SELECT unpvt.*
    FROM #P
    CROSS APPLY (VALUES
    (CustNo, 1, TimeAmount)
    ,(CustNo, 2, ExpenseAmount)
    )unpvt(CustNo,AmountType,Amount)
    ;

    And thank you for the readily consumable test data!  Nicely done!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Many thanks for all the replies everyone!

    If using CROSS APPLY is there a way to do a WHERE clause so that we don't create line items for a $0 dollar value?  If the TimeAmount or ExpenseAmount is $0 we would not want to convert the row and process the record.

  • that is just a normal where clause you would add as in any other case.

     

    where p.x <> 0 or p.y <> 0 -- this processes the row if either has a value.  if one value is zero that is still added to the destination table

     

     

  • This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply