2

Is there a way to aggregate strings in Azure SQL Data Warehouse similar to the string_agg function in SQL Server?

I have records with strings that I want to concatenate into a single string.

SELECT string_agg(string_col, ',') FROM table1

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql

Erik Shilts
  • 4,389
  • 2
  • 26
  • 51
  • this method should work https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server – JamieD77 Nov 16 '17 at 18:49
  • 1
    Here is an old answered link that may work https://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings – Murshed_MSFT Nov 16 '17 at 19:15

2 Answers2

2

Update Jan 2019: As at Jan 2019 STRING_SPLIT is available in SQL Data Warehouse as per here.

Update Jul 2019: As at Jul 2019 STRING_AGG is available in SQL Data Warehouse as per here.

Azure SQL Data Warehouse is an MPP system designed to hold billions of records and terabytes of data, so as a first port of call you should strongly consider if it's the right place to do string manipulation. It's also kind of expensive depending on what DWU you are running at. Have a read through this anti-patterns article.

Azure SQL Data Warehouse Workload Patterns and Anti-Patterns https://blogs.msdn.microsoft.com/sqlcat/2017/09/05/azure-sql-data-warehouse-workload-patterns-and-anti-patterns/

If you really need to do this in your warehouse then I did do a recent example specifically for Azure SQL Data Warehouse using a procedural approach:

Does Azure SQL Data Warehouse have a way to split strings?

wBob
  • 13,710
  • 3
  • 20
  • 37
  • IMO I would see STRING_AGG as a standard final "reduce" aggregation step on a a grouping , much the same same as other aggregates like `SUM`, `COUNT` etc. FWIW [AWS RedShift](https://docs.aws.amazon.com/redshift/latest/dg/r_LISTAGG.html) supports the equivalent functionality. – StuartLC Oct 01 '18 at 16:19
  • If you think it's worthwhile @StuartLC you can upvote the request. Looks like they might have started work on it according to here: https://feedback.azure.com/forums/307516-sql-data-warehouse/suggestions/20215162-add-split-part-function-to-sql-dw – wBob Oct 01 '18 at 16:57
  • I've appended a comment and upvoted - [the link is here](https://feedback.azure.com/forums/307516-sql-data-warehouse/suggestions/14876763-stuff-for-xml) – StuartLC Oct 01 '18 at 17:11
0

Another way to achieve this to use the PIVOT function which is available in Azure SQL Data Warehouse. It's descibred in detail in this TechNet article: Concatenating Columns over Rows in APS/PDW AU4 using T-SQL

Ward
  • 39
  • 7
  • As of the July 2019 release of Azure Data Warehouse, STRING_AGG is implemented. https://learn.microsoft.com/en-us/azure/sql-data-warehouse/release-notes-10-0-10106-0 – Ward Jul 24 '19 at 07:50