String aggregation functions (remove duplicate values) _mssql2005

--Function: Provides alternative aggregate functions for strings
--Note: For example, the following data
--test_id Test_value
--------------------
' A ' 01,03,04 '
' A ' 02,04 '
' B ' 03,04,08 '
' B ' 06,08,09 '
' C ' 09 '
' C ' 10 '
--the function that is converted to the Test_vlaue column aggregation, and the values in the aggregated string are not duplicated

--test_id Test_value
--------------------
' A ' 01,03,04,02 '
' B ' 03,04,08,06,09 '
' C ' ' 09,10 '

--Code-------------------------------------------Go
Go
If OBJECT_ID (n ' dbo.merge ', n ' FN ') is not null
Drop function Dbo.merge
Go
--function function: string aggregation and elimination of duplicate values
Create function Dbo.merge
(
@test_id varchar (50)
)
Returns varchar (50)
As
Begin
--String Aggregation-------------------------START
DECLARE @s varchar (8000)
Set @s = '

Select
@s = @s + Test_value + ', '
From Test_a
where test_id = @test_id
--String Aggregation-------------------------end

--Remove duplicate values-------------------------START
DECLARE @value varchar (8000)--stores the value before the first comma
DECLARE @result varchar (8000)--storing the middle string of unique values
Set @result = '

--an occasion of value
While CHARINDEX (', ', @s) <> 0
Begin
--the value before the first comma
Set @value = Left (@s,charindex (', ', @s)-1)

--the value before the first comma does not appear in the result
If Charindex (', ' + @value + ', ', ', ' + @result) = 0
Begin
--Join the middle string
Set @result = @result + @value + ', '
End

--Remove the first value and the following comma (Exclude method), continue to cycle the judgment
Set @s = Right (@s, (Len (@s)-charindex (', ', @s))
End

Set @s = @result
--Remove duplicate value-------------------------end

Return to Left (@s,len (@s)-1)
End
Go

If OBJECT_ID (n ' test_a ', n ' U ') is not null
drop table Test_a
Go

CREATE TABLE Test_a
(
test_id varchar (50),
Test_value varchar (50)
)

INSERT INTO Test_a
Select ' A ', ' 01,03,04 ' UNION ALL
Select ' A ', ' 02,04 ' UNION ALL
Select ' B ', ' 03,04,08 ' UNION ALL
Select ' B ', ' 06,08,09 ' UNION ALL
Select ' C ', ' UNION ALL '
Select ' C ', ' 10 '

Select
TEST_ID,
Test_value = Dbo.merge (test_id)
From Test_a
GROUP BY test_id