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 2016,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
     ID, STRING_AGG(Name, ',') AS NAMES
FROM @TempTable
GROUP BY ID 


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



3 comments: