Select Column Values As Comma Separated String In Azure Synapse Sql Server

Here is my requirement Data 1 Umesh usc@gmail.com 2 Umesh usc@yahoo.com 3 Mahesh msc@zyx.com Out put 1 Umesh usc@gmail.com,usc@yahoo.com 2 Mahesh msc@zyx.com FOR XML is working

Solution 1:

Azure Synapse Analytics does not support FOR XML but it does support STRING_SPLIT and STRING_AGG as table functions. You need STRING_AGG for your query. A simple example, tested on a dedicated SQL pool:

IF OBJECT_ID('tempdb..#tmp') ISNOTNULLDROPTABLE #tmp;

CREATETABLE #tmp (
    rowId           INTPRIMARY KEY NONCLUSTERED NOT ENFORCED,
    someName        VARCHAR(50),
    someEmail       VARCHAR(50)
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    HEAP
);

INSERTINTO #tmp ( rowId, someName, someEmail )
SELECT1, 'Umesh', 'usc@gmail.com'UNIONALLSELECT2, 'Umesh', 'usc@yahoo.com'UNIONALLSELECT3, 'Mahesh', 'msc@zyx.com';


SELECT someName, STRING_AGG( someEmail, ',' ) resultFROM #tmp
GROUPBY someName

My results: