CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Brian Crist
Brian Crist

Reputation: 814

How to merge data in multiple rows to one row in SQL Server?

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

enter image description here

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

Upvotes: 0

Views: 111

Answers (1)

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

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>', ',')

Upvotes: 1

Related Questions