create table tb (ID int,col varchar(50))
insert tb select 1, '1,2,3,4 '
union all select 1, '1,2,5,6 '
union all select 2, '1,2,3 '
union all select 2, '1,7 '
实现为
1 '1,2'
2 '1'
请不要发老大的例子,看不懂
insert tb select 1, '1,2,3,4 '
union all select 1, '1,2,5,6 '
union all select 2, '1,2,3 '
union all select 2, '1,7 '
实现为
1 '1,2'
2 '1'
请不要发老大的例子,看不懂
insert tb select 1, '1,2,3,4 '
union all select 1, '1,2,5,6 '
union all select 2, '1,2,3 '
union all select 2, '1,7 '
goSELECT TOP 8000 id = identity(int,1,1) INTO tmp FROM syscolumns a, syscolumns b
select id , col from
(
SELECT A.ID, col = SUBSTRING(A.col, B.ID, CHARINDEX(',', A.col + ',', B.ID) - B.ID)
FROM tb a, tmp B
WHERE SUBSTRING(',' + a.col, B.id, 1) = ','
) t
group by id , col having count(*) >= 2
order by id , col drop table tb,tmp/*
id col
----------- --------------------------------------------------
1 1
1 2
2 1(所影响的行数为 3 行)
*/
insert tb select 1, '1,2,3,4 '
union all select 1, '1,2,5,6 '
union all select 2, '1,2,3 '
union all select 2, '1,7 '
go--创建一临时表
SELECT TOP 8000 id = identity(int,1,1) INTO tmp1 FROM syscolumns a, syscolumns b
select id , col into tmp2 from
(
SELECT A.ID, col = SUBSTRING(A.col, B.ID, CHARINDEX(',', A.col + ',', B.ID) - B.ID)
FROM tb a, tmp1 B
WHERE SUBSTRING(',' + a.col, B.id, 1) = ','
) t
group by id , col having count(*) >= 2
go
--创建一个合并的函数
create function f_hb(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(col as varchar) from tmp2 where id = @id
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
select distinct id ,dbo.f_hb(id) as col from tmp2drop table tb,tmp1,tmp2drop function f_hb/*
id col
----------- -------
1 1,2
2 1
(所影响的行数为 2 行)
*/