DECLARE @tb Table(   
  CompanyID INT,   
  CompanyCodes VARCHAR(100)  
)  
 
insert into @tb select 1,'1|2'  
union all select 2,'1|2|3'  
union all select 3,'1|2|3|4'
union all select 4,'1|2|3|4|5'

select a.CompanyID,
    CompanyCodes = substring(a.CompanyCodes,b.number,charindex('|',a.CompanyCodes+'|',b.number)-b.number)
from @tb a join master..spt_values b 
    on b.[type] = 'p' and b.number between 0 and len(a.CompanyCodes)
        and substring('|'+a.CompanyCodes,b.number,1) = '|'

/****************

CompanyID   CompanyCodes
----------- ----------------------------------------------------------------
1           1
1           2
2           1
2           2
2           3
3           1
3           2
3           3
3           4
4           1
4           2
4           3
4           4
4           5

(14 行受影响)

------解决方案--------------------
DECLARE @tb Table(   
   F1 varchar(12),   
   F2 VARCHAR(100)  
)  
  
insert into @tb select 'a','1,2,5'  
union all select 'b','1,3'  
union all select 'c','3,5'

select a.F1,b.vx
from  
(select F1,cast('<root><v>'+REPLACE(F2,',','</v><v>')+'</v></root>' as xml) as x from @tb) a
outer apply(
   select vx=N.v.value('.','varchar(100)') from a.x.nodes('/root/v') N(v)
) b


------解决方案--------------------
凑个热闹
SQL code
create table tb(F1 char(1),F2 varchar(10))
insert into tb 
  select 'a','1,2,5' union 
  select 'b','1,3' union 
  select 'c','3,5'  
  
create function f(@str varchar(20))
returns @t table (F varchar(2))
as
begin
    set @str=@str+','
    while CHARINDEX(',',@str)>0
       begin
          insert into @t 
           select left(@str,CHARINDEX(',',@str)-1)
           set @str=right(@str,len(@str)-CHARINDEX(',',@str))
       end 
    return   
end
select a.F1,b.* from tb a
cross apply f(a.F2)b

/