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.



9 comments:

  1. A IEEE project is an interrelated arrangement of exercises, having a positive beginning and end point and bringing about an interesting result in Engineering Colleges for a particular asset assignment working under a triple limitation - time, cost and execution. Final Year Project Domains for CSE In Engineering Colleges, final year IEEE Project Management requires the utilization of abilities and information to arrange, plan, plan, direct, control, screen, and assess a final year project for cse. The utilization of Project Management to accomplish authoritative objectives has expanded quickly and many engineering colleges have reacted with final year IEEE projects Project Centers in Chennai for CSE to help students in learning these remarkable abilities.



    Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
    Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

    ReplyDelete
  2. I am having this error
    'STRING_AGG' is not a recognised built in function
    Reason please...

    ReplyDelete
    Replies
    1. It might be due to version compatibility issue. As per below microsoft link it will work from 2017 onward. Please check your version
      https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15

      Delete
    2. Yes I think it's introduced 2017 onward.

      Delete
  3. If Column has same name multiple times then string_agg is providing multiple times but where as stuff gives it single time. is there a way to get single name even if name repeats multiple times using String_agg

    ReplyDelete