/*
create function [dbo].[m_split2](@c varchar(max),@split varchar(2))   
  returns @t table(col varchar(max))   
  as   
    begin   
      while(charindex(@split,@c)<>0)   
        begin   
            if(substring(@c,1,charindex(@split,@c)-1)!=' ')
            begin
          insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))   
            end
             set @c = stuff(@c,1,charindex(@split,@c),'')    
        end   
        if(@c!=' ')
        begin
      insert @t(col) values (@c) 
      end  
      return   
end
*/
declare @T nvarchar(max)
set @T=
'离线记录^songlijun|8888888888|3500320083208#3500600520890#3500741122924#3500790143280#
3501010510869#3500741129750%离线记录^xieyuan|0540002070|3500320083208#3500600520890#350
0741122924#3500790143280#3501010510869#3500741129750$5480011708|3500320083208#350060052
0890#3500741122924#3500790143280#3501010510869#3500741129750$5480011701|3500320083208#3
500600520890#3500741122924#3500790143280#3501010510869#3500741129750$0540002070|3500320
083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750%离线记录^x
ieyuan|0540002070|3500320083208#3500600520890#3500741122924#3500790143280#3501010510869
#3500741129750$0540002070|3500320083208#3500600520890#3500741122924#3500790143280#35010
10510869#3500741129750'

select * into #t from [dbo].[m_split2](@T,'%')

update #t set col=replace(col,' ','')
update #t set col=replace(col,char(13),'')
update #t set col=replace(col,char(10),'')

select 
substring(col,charindex('^',col)+1,
charindex('|',col)-charindex('^',col)-1) as id,
right(col,len(col)-charindex('|',col)) as [value] into #t2
from #t
drop table #t

select id, left(value,charindex('|',value)-1) as nid,
right(value,len(value)-charindex('|',value)+1) as value into #t3 from (
select A.id, B.value 
from( 
    select id, [value] = 
convert(xml,' <root> <v>' + replace([value], '$', ' </v> <v>') + ' </v> </root>') from #t2 
)A 
outer apply( 
    select value = N.v.value('.', 'varchar(100)') from A.[value].nodes('/root/v') N(v) 
)B 
) aa

drop table #t2

select A.id as 物流员, A.nid as 站点编号,B.value as 编号集合 
from( 
    select id,nid, [value] = 
convert(xml,' <root> <v>' + replace([value], '#', ' </v> <v>') + ' </v> </root>') from #t3 
)A 
outer apply( 
    select value = N.v.value('.', 'varchar(100)') from A.[value].nodes('/root/v')