create table tb(ID int,name varchar(20)) insert into tb select 1,'1,2,3,4' union all select 2,'7,3,2,4' create function f_name(@name varchar(20)) returns @tb table (name varchar(10)) begin set @name=@name+',' while charindex(',',@name)>0 begin insert into @tb select left(@name,charindex(',',@name)-1) set @name=right(@name,len(@name)-charindex(',',@name)) end return endselect a.id,b.name from tb a cross apply f_name(a.name)b/* id name ----------- ---------- 1 1 1 2 1 3 1 4 2 7 2 3 2 2 2 4(8 row(s) affected)
insert into tb
select 1,'1,2,3,4' union all
select 2,'7,3,2,4' create function f_name(@name varchar(20))
returns @tb table (name varchar(10))
begin
set @name=@name+','
while charindex(',',@name)>0
begin
insert into @tb select left(@name,charindex(',',@name)-1)
set @name=right(@name,len(@name)-charindex(',',@name))
end
return
endselect a.id,b.name from tb a cross apply f_name(a.name)b/*
id name
----------- ----------
1 1
1 2
1 3
1 4
2 7
2 3
2 2
2 4(8 row(s) affected)