--测试表
if OBJECT_ID('test') is not null
drop table test
go
create table test
(
[key] varchar(20)
)
go
insert test
select 'a,b' union all
select 'b,c'
select * from test
/*
key
--------------------
a,b
b,c
(2 行受影响)
*/
--拆分去重
select
distinct
SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number) as [key]
into #a
from
test a,master..spt_values
where
number >=1 and number<=len([key])
and [type]='p'
and substring(','+[key],number,1)=','
select * from #a
/*
key
--------------------
a
b
c
(3 行受影响)
*/
--合并
SELECT distinct
stuff((SELECT ','+[key] FROM #a
FOR XML PATH('')
),1,1,'') AS [key]
FROM #a a
/*
key
---------------------
a,b,c
(1 行受影响)
*/
SQL Server2005 拆分-去重-合并
最新推荐文章于 2025-06-14 16:40:37 发布