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

Question

Post reply

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

rjjh78

SSCrazy

Points: 2774

January 19, 2023 at 8:48 pm

#4139727

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 2 months, 1 week ago by rjjh78.

January 24, 2023 at 10:17 pm

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

pietlinden SSC Guru Points: 63319 More actions · Answer 1

Something like this?

SELECT CustNo, TimeAmount AS Amount, 1 AS ExpenseType FROM #P
UNION ALL
SELECT CustNo, ExpenseAmount, 2 FROM #P;

Ed B Hall of Fame Points: 3493 More actions · Answer 2

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

frederico_fonseca SSCoach Points: 15633 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1002548 More actions · Answer 4

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)

rjjh78 SSCrazy Points: 2774 More actions · Answer 5

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.

frederico_fonseca SSCoach Points: 15633 More actions · Answer 6

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