—
功能
:
提供字符串的替代聚合函数
—
说明
:
例如
,
将下列数据
–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,03,04,02’
‘b’
‘03,04,08,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,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