String aggregate function (remove duplicate values)

-- Function: Provides replacement Aggregate functions for strings.
-- Note: for example, convert 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'
-- Convert to the aggregate function of the test_vlaue column, and the values in the aggregated string are not repeated.

-- 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: String aggregation and deduplication
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) -- store the value before the first comma
Declare @ result varchar (8000) -- stores the intermediate string of the unique value
Set @ result =''

-- Valuable occasions
While charindex (',', @ s) <> 0
Begin
-- Take 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
-- Add an intermediate string
Set @ result = @ result + @ value + ','
End

-- Remove the first value and the following comma (removal method) and continue to judge cyclically
Set @ s = right (@ s, (len (@ s)-charindex (',', @ s )))
End

Set @ s = @ result
-- Remove duplicate values ----------------------- END

Return 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', '09' union all
Select 'C', '10'

Select
Test_id,
Test_value = dbo. merge (test_id)
From test_a
Group by test_id