I want to merge data in multiple rows to one row in SQL Server using with FOR XML PATH, but same it's not work .I try with
SELECT STUFF((SELECT ',' + ZZ.UNAME FROM X XX
INNER JOIN Y YY ON YY.UUID = XX.UUID
INNER JOIN Z ZZ ON ZZ.UID = YY.UID
WHERE XX.TID = 'T1'
FOR XML PATH('')),1,1,'') AS [MERGE_NAME]
As result same
MERGE_NAME
ABC,XYZ
Can you tell me some mistakes or something wrong in here ? Thank you
You can try this:
DECLARE @merge_name VARCHAR(1000) = ''
SELECT @merge_name = @merge_name + ',' + ZZ.UNAME FROM X XX
INNER JOIN Y YY ON YY.UUID = XX.UUID
INNER JOIN Z ZZ ON ZZ.UID = YY.UID
WHERE XX.TID = 'T1'
SELECT RIGHT(@merge_name, LEN(@merge_name)-1)
If you use STUFF, you can try:
SELECT REPLACE(REPLACE(STUFF((SELECT ZZ.UNAME
FROM X XX
INNER JOIN Y YY ON YY.UUID = XX.UUID
INNER JOIN Z ZZ ON ZZ.UID = YY.UID
WHERE XX.TID = 'T1' FOR XML PATH('')),1,0,''), '<Name>', ''), '</Name>', ',')
