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