0

Here is my requirement

Data

1 Umesh [email protected]
2 Umesh [email protected]
3 Mahesh [email protected]

Out put
1 Umesh [email protected],[email protected]
2 Mahesh [email protected]

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', '[email protected]'
UNION ALL
SELECT 2, 'Umesh', '[email protected]'
UNION ALL
SELECT 3, 'Mahesh', '[email protected]';


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