dawei
–功能:提供字符串的替代聚合函数
–说明:例如,将下列数据
–test_id test_value
——————–
‘a’ ‘01,03,04’
‘a’ ‘02,04’
‘b’ ‘03,04,08’
‘b’ ‘06,08,09’
‘c’ ’09’
‘c’ ’10’
–转换成test_vlaue列聚合后的函数，且聚合后的字符串中的值不重复 –test_id test_value
——————–
‘a’ ‘01,02’
‘b’ ‘03,06,09’
‘c’ ‘09,10’ –代码——————————————-GO
GO
if object_id(N’dbo.merge’,N’FN’) is not null
drop function dbo.merge
GO
–函数功能:字符串聚合及去除重复值
create function dbo.merge
(
@test_id varchar(50)
)
returns varchar(50)
as
begin
–字符串聚合————————-START
declare @s varchar(8000)
set @s = ” select
@s = @s + test_value + ‘,’
from test_a
where test_id = @test_id
–字符串聚合————————-END –去除重复值————————-START
declare @value varchar(8000)–存储第一个逗号前的值
declare @result varchar(8000)–存储唯一值的中间字符串
set @result = ” –有值的场合
while charindex(‘,’,@s) <> 0
begin
–取第一个逗号前的值
set @value = left(@s,charindex(‘,@s) -1) –第一个逗号前的值没在结果中出现
if charindex(‘,’ + @value + ‘,’ + @result) = 0
begin
–加入中间字符串
set @result = @result + @value + ‘,’
end –去除第一个值以及后面的逗号(剔除法),继续循环判断
set @s = right(@s,(len(@s) – charindex(‘,@s)))
end set @s = @result
–去除重复值————————-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,04′ union all
select ‘a’,’02,04′ union all
select ‘b’,’03,08′ union all
select ‘b’,’06,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
