設置為有四個字段
id A B C
001 20.00 010,020
002 30.00 010
003 50.00 010,020select * from
(select id,A,B from tb
union select id,A,C from tb)a
order by id
id A B C
001 20.00 010,020
002 30.00 010
003 50.00 010,020select * from
(select id,A,B from tb
union select id,A,C from tb)a
order by id
create table tb(col1 varchar(10),col2 decimal(10,2),col3 varchar(8000))
insert tb select '001',20.00,'010,020'
union all select '002',30.00,'010'
union all select '003',50.00,'010,020'
go--查询
declare @i int
select @i=max(len(col3)) from tb
set rowcount @i
select id=identity(int) into #t from syscolumns a,syscolumns b
set rowcount 0
select a.col1
,col2=cast(a.col2/(len(a.col3)-len(replace(a.col3,',',''))+1) as decimal(10,2))
,col3=substring(a.col3,b.id,charindex(',',a.col3+',',b.id)-b.id)
from tb a,#t b
where b.id<=len(a.col3) and substring(','+a.col3,b.id,1)=','
drop table #t
go--删除测试
drop table tb/*--测试结果col1 col2 col3
---------- ------------ --------
001 10.00 010
001 10.00 020
002 30.00 010
003 25.00 010
003 25.00 020(所影响的行数为 5 行)--*/