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, ''
',' + 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.