question

AlekyaSeemakurtySri-1788 avatar image
0 Votes"
AlekyaSeemakurtySri-1788 asked AlekyaSeemakurtySri-1788 commented

Using Azure Data factory, how to merge multiple rows into one row?

I have data coming up from the source in this way :

Key | Value
10 | 101
10 | 102
10 | 103
10 | 104
20 | 201
30 | 105
30 |106
30 | 107
40 | 432
40 | 444
50 | 120

My output should look as follow:

Key | value
10 | 101,102,103,104
20 | 201
30 | 105,106,107
40 | 432,444
50 | 120

This in SQL Server could by acheived by FOR XML PATH or String_Agg(), but how can we do the same using Azure Data factory transformations? Unable to find String_Agg() in Azure Data factory. Is there any other method for this? Please help asap.

azure-data-factoryazure-functionsazure-cloud-services
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.

MarkKromer-2402 avatar image
0 Votes"
MarkKromer-2402 answered KranthiPakala-MSFT commented

Use an Aggregate transformation in a data flow with grouping and the collect() expression function.

https://docs.microsoft.com/en-us/azure/data-factory/data-flow-script#string_agg-equivalent

· 1
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 @AlekyaSeemakurtySri-1788,

Following up to see if the above information from Mark was helpful to achieve your requirement. If you still need assistance do let us know.



Thanks

0 Votes 0 ·
AlekyaSeemakurtySri-1788 avatar image
0 Votes"
AlekyaSeemakurtySri-1788 answered AlekyaSeemakurtySri-1788 commented

Thanks a lot Mark, it was helpful. However, how can I get distinct values from the groups? collect() is fetching all the values (including duplicates)

· 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.

Set the grouping appropriately in your aggregate transformation settings to group by the values that you wish to make distinct

0 Votes 0 ·

My source has data as follow:
Key |Value|Amount
10 | 101| 10
10 | 101| 7
10 | 101| 5
10 | 101| 1
10 | 101| 8
10 | 101| 90
10 | 102|1
10 | 102| 70
10 | 103|24
10 | 104|12
20 | 201|22
20 | 201|24
30 | 105|34
30 |106 |45
30 | 107| 42
40 | 432| 67
40 | 444|78
50 | 120|10

My output should look as follow:

Key | value
10 | 101,102,103,104
20 | 201
30 | 105,106,107
40 | 432,444
50 | 120

But with the connect and conversion, I am getting values this way:
Key | value
10 | 101,101,101,101,101,101,102,102,103,104
20 | 201,201
30 | 105,106,107
40 | 432,444
50 | 120

I did sort on Key and Value, and then use Aggregator to group by on Key. Still the result is same with collect()

0 Votes 0 ·