String aggregation function (de duplicates)

Time:2019-12-18

–Function: provides an alternative aggregate function for Strings
–Description: for example, add 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’
–It is converted to the function after the aggregation of the test_vlaue column, and the value in the aggregated string is 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 function: String aggregation and de duplication
create function dbo.merge
(
@test_id varchar(50)
)
returns varchar(50)
as
begin
–String aggregation
declare @s varchar(8000)
set @s = ”

select
@s = @s + test_value + ‘,’
from test_a
where test_id = @test_id
–String aggregation

–Remove duplicate value ———— start
Declare @ value varchar (8000) — store the value before the first comma
Declare @ result varchar (8000) — intermediate string to store unique value
set @result = ”

–Worthy occasion
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 intermediate string
set @result = @result + @value + ‘,’
end

–Remove the first value and the comma after it (elimination method), and continue to cycle judgment
set @s = right(@s,(len(@s) – charindex(‘,’,@s)))
end

set @s = @result
–Remove duplicate value

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