0

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 fine in SQL on prim sql server but same query is not working in Synapse

umesh.chape
  • 2,895
  • 5
  • 16
  • 17

1 Answers1

2

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') IS NOT NULL DROP TABLE #tmp;

CREATE TABLE #tmp (
    rowId           INT  PRIMARY KEY NONCLUSTERED NOT ENFORCED,
    someName        VARCHAR(50),
    someEmail       VARCHAR(50)
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    HEAP
);

INSERT INTO #tmp ( rowId, someName, someEmail )
SELECT 1, 'Umesh', 'usc@gmail.com'
UNION ALL
SELECT 2, 'Umesh', 'usc@yahoo.com'
UNION ALL
SELECT 3, 'Mahesh', 'msc@zyx.com';


SELECT someName, STRING_AGG( someEmail, ',' ) result
FROM #tmp
GROUP BY someName

My results:

results

wBob
  • 13,710
  • 3
  • 20
  • 37
  • PERFECT! I;m glad MSFT thought ahead if they were going to remove a feature they would add it in another waY! – Sauron Sep 23 '21 at 19:45
  • 1
    I always thought the xml trick for concatenating strings was a bit of a hack so I’m glad there’s a proper way of doing it now. How about an upvote @Sauron?? : ) – wBob Sep 23 '21 at 19:52
  • @wBob already did BOBBY!! – Sauron Sep 24 '21 at 18:14