question

porntipgurusvasti-0309 avatar image
0 Votes"
porntipgurusvasti-0309 asked ShaikMaheer-MSFT commented

Combine the varying number of rows to one row in Data Factory in Azure

I am so new in Azure Data Factory, and I have a problem solving this problem. I wanted to combine varying number of rows in to one row base on the substring of the first column , I know that I can use collect() but the problem is I don't have a key column.

Here is my data in csv file looks like

PREC001 NY
DREC12023 ALKBY23
PREC001 NJ
DREC23545
AREC20210728 2.30
...

And I wanted the output to be like

PREC001 NY DREC12023 ALKBY23
PREC001 NJ DREC23545 AREC20210728 2.30
....

So I wanted to combine multiple rows to one and start the new row if the row starts with 'PREC', you can see that the number of rows that will be combined is varying. I have been looking for days for the solution but found none, can anyone help me with this.

Thank you in advance

azure-data-factory
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @porntipgurusvasti-0309 ,

Following up to check is below provided answer helps you? If yes Please Accept Answer. Accepting answer helps community too. Please let us know if any further queries. Thank you.

0 Votes 0 ·

Hi @porntipgurusvasti-0309 ,

Just checking is below provided answer helps you? If yes Please Accept Answer. Accepting answer helps community too. Please let us know if any further queries. Thank you.

0 Votes 0 ·

1 Answer

ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered

Hi @porntipgurusvasti-0309 ,

Thank you for posting query in Microsoft Q&A Platform.

Approach 1:
We can achieve this using ADF data flows, but we should have an Key column with similar values for the rows to combine. Kindly check below video to understand it better. If you are able to get Key column from source then this approach will work.
https://www.youtube.com/watch?v=0W8RB72thec

Approach 2:
If you cant get Key column from source then try to load data in to SQL table and then using cursor and while loop try to generate a new Key column and then using Group by and **string_aggr() f**unction you can combine multiple rows in to single row.

Hope this will help. Thank you. Please let us know if you have any further questions. Thank you.


  • Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.