存储过程:通过字符串中的分界符，把一列分为多列！

 

create      table  tb (cno  varchar ( 10 ),msg  varchar ( 200 ))
insert   into  tb
select   ' 0001 ' , ' YK|43.00|43.00| '   union   all
select   ' 0002 ' , ' ZX|2350.00|2350.00| '   union   all
select   ' 0003 ' , ' 2032|MD|15|120.00|120.00|4518105496132709,12/2006,0,,| '  
 

declare   @max   int , @i   int
declare   @sql   varchar ( 8000 )
select    @max = len (msg) - len ( replace (msg, ' | ' , '' )), @i = 1 , @sql = ''   from  tb
select  cno,msg + replicate ( ' | ' , @max - len (msg) + len ( replace (msg, ' | ' , '' )))  as  msg  into  tbx  from  tb
while   @i <= @max
begin
     select   @sql = @sql + ' ,col ' + ltrim ( @i ) + '   varchar(30)  ' , @i = @i + 1
end
set   @sql = ' create table tbxx(cno varchar(10) ' + @sql + ' ) '
exec  ( @sql )
insert   into  tbxx(cno)
select  cno  from  tbx

set   @i = 1
while   @i <= @max
begin
select    @sql = ' declare @tmp table(cno varchar(10),head  varchar(30))
insert into @tmp(cno,head)
select cno,left(msg,charindex( '' | '' ,msg)-1) from tbx
update tbx set msg=stuff(msg,1,charindex( '' | '' ,msg), '''' )
update a set a.col ' + ltrim ( @i ) + ' =b.head from tbxx a,@tmp b where a.cno=b.cno '
exec  ( @sql )
set   @i = @i + 1
end

  select   *    from  tbxx

  drop   table  tb,tbx,tbxx
 

-- result
/*
cno    col1    col2    col3    col4    col5    col6
--------              ------               ------               --------              ----------           -----------          --------------------------------
0001    YK    43.00    43.00            
0002    ZX    2350.00    2350.00            
0003    2032    MD    15    120.00    120.00    4518105496132709,12/2006,0,,
*/