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://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql
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:
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
