create table tb(ID varchar(10),BUMENID varchar(10)) insert into tb values('1', '1,3,5') insert into tb values('2', '2,4,6') insert into tb values('3', '2,3,5') go --建立一个辅助的临时表 SELECT TOP 8000 id = identity(int,1,1) INTO temp FROM syscolumns a, syscolumns b go --创建一个合并的函数 create function f_hb(@BUMENID varchar) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + ',' + cast(id as varchar) from ( SELECT A.ID, BUMENID = SUBSTRING(A.BUMENID, B.ID, CHARINDEX(',', A.BUMENID + ',', B.ID) - B.ID) FROM tb A, temp B WHERE SUBSTRING(',' + a.BUMENID, B.id, 1) = ',' ) t where BUMENID = @BUMENID set @str = right(@str , len(@str) - 1) return(@str) End go--调用自定义函数得到结果: select distinct BUMENID ,dbo.f_hb(BUMENID) as id from ( SELECT A.ID, BUMENID = SUBSTRING(A.BUMENID, B.ID, CHARINDEX(',', A.BUMENID + ',', B.ID) - B.ID) FROM tb A, temp B WHERE SUBSTRING(',' + a.BUMENID, B.id, 1) = ',' ) tdrop table tb,temp drop function f_hb/* BUMENID id ---------- --- 1 1 2 2,3 3 1,3 4 2 5 1,3 6 2 (所影响的行数为 6 行) */
insert into tb values('1', '1,3,5')
insert into tb values('2', '2,4,6')
insert into tb values('3', '2,3,5')
go
--建立一个辅助的临时表
SELECT TOP 8000
id = identity(int,1,1)
INTO temp FROM syscolumns a, syscolumns b
go
--创建一个合并的函数
create function f_hb(@BUMENID varchar)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(id as varchar) from
(
SELECT
A.ID,
BUMENID = SUBSTRING(A.BUMENID, B.ID, CHARINDEX(',', A.BUMENID + ',', B.ID) - B.ID)
FROM tb A, temp B
WHERE SUBSTRING(',' + a.BUMENID, B.id, 1) = ','
) t
where BUMENID = @BUMENID
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct BUMENID ,dbo.f_hb(BUMENID) as id from
(
SELECT
A.ID,
BUMENID = SUBSTRING(A.BUMENID, B.ID, CHARINDEX(',', A.BUMENID + ',', B.ID) - B.ID)
FROM tb A, temp B
WHERE SUBSTRING(',' + a.BUMENID, B.id, 1) = ','
) tdrop table tb,temp
drop function f_hb/*
BUMENID id
---------- ---
1 1
2 2,3
3 1,3
4 2
5 1,3
6 2
(所影响的行数为 6 行)
*/