Search code examples
azuressisetlazure-data-factorydata-transform

Azure Data Factory / Aggregate as comma separated string

I want to aggregate by data using Aggregate.

Here is the scenario:

I've a table having values as below:

Key | Value
1   | v1
1   | v2
2   | v1
2   | v3

After performing aggregation, I want output as below:

Key | Value
1   | v1, v2
2   | v1, v3

I tried to find String_Agg function which is not available in ADF.

Solution

  • Faced the same issue recently. I ended up sinking the table, then creating a new dataflow that would query that table using SQL GROUP BY and STRING_AGG().

    MAY 2020 update:

    Microsoft released a new feature - collect transformation: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-expression-functions#collect It can be used in aggregation step and produce an array with the values you need. It can be then followed by the derived column step to cast the string array toString().