Using STUFF and STRING_AGG In SQL Server

        The STUFF function is insert a string into another string .It removes the specified length of characters and insert new string to matching position 

   SELECT STUFF('Hi Rahul', 1, 2, 'Mr') AS Name

       From the above query it will replace Hi to Mr. You can use the STUFF with XML Path for concatenate the column data easily.

DECLARE @TempTable TABLE(Id INT,Name VARCHAR(20))

INSERT INTO @TempTable

SELECT 1, 'Raju'

UNION ALL

SELECT 2, 'Manu'

UNION ALL

SELECT 3, 'Vinu'

SELECT * FROM @TempTable

SELECT

   STUFF(

                (SELECT 
                ',' + Name 
           FROM @TempTable FOR XML PATH ('')), 1, 1, ''

               ) AS NAMES

FROM @TempTable WHERE ID = 1

       

      To handle this exact scenario, MS introduced new functionality called STRING_AGG in SQL 2017. STRING_AGG Concatenates the values of string expressions and places separator values between them.

DECLARE @TempTable TABLE(ID INT,Name VARCHAR(20))

INSERT INTO @TempTable

SELECT 1, 'Raju'

UNION ALL

SELECT 2, 'Manu'

UNION ALL

SELECT 3, 'Vinu'

SELECT * FROM @TempTable

SELECT

     STRING_AGG(Name, ',') AS NAMES

FROM @TempTable

      I believe this would be help full method for what you are trying to accomplish with the XML/STUFF method.