asked
How to create multiple rows from a single row
Hi everyone I am migrating data from one crm to another and using ssis to transform then Scribe to populate the data and having trouble with one particular target entity. I'm having to make it up out of the existing Invoice data and so I have Pending and Paid invoices. Unpaid = easy. But for the part paid invoices I have to make up two new rows out of one to go into the related target entity - one for the paid bit (where amount paid goes into Value column with status of Paid), and another row for the amount remaining (where amount remaining also goes into the Value column with status of Unpaid). I've had a look at UNPIVOT but can't see how to apply it to this scenario. I may have just been looking at it too long. Any help whatsoever would be gratefully received. Many thanks, Jules
As John said, we need a bit more information. However union could be handy here select amount, paid as status where union select amount, unpaid as status where and then it could be used as your source query
Thank you both for responding so quickly, I've just been playing with a quick n dirty fix by creating 2 new views, one for each scenario, then doing a union on the results - seems to be working. I've no doubt it's not the best way to get the results I wanted but whatever gets me there is good for me. Thanks once again Best, Jules
19 People are following this question.
