if object_id('pubs..tb') is not null
drop table tb
go
create table tb(f1 varchar(10),f2 int)
insert into tb(f1,f2) values('B+C+D+E',1)
insert into tb(f1,f2) values('B+C',2)
insert into tb(f1,f2) values('B',3)
go
-- 建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
SELECT
f1 = SUBSTRING(tb.f1, B.ID, CHARINDEX('+', tb.f1 + '+', B.ID) - B.ID) ,
tb.f2
FROM tb, # B
WHERE SUBSTRING('+' + tb.f1, B.id, 1) = '+'
ORDER BY 1,2
GO
DROP TABLE tb,#
/*
f1 f2
---------- -----------
B 1
B 2
B 3
C 1
C 2
D 1
E 1
if object_id('pubs..tb') is not null
drop table tb
go
create table tb(f1 varchar(10),f2 int)
insert into tb(f1,f2) values('B+C',1)
insert into tb(f1,f2) values('B+C',2)
go
-- 建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b
SELECT
f1 = SUBSTRING(tb.f1, B.ID, CHARINDEX('+', tb.f1 + '+', B.ID) - B.ID) ,
tb.f2
FROM tb, # B
WHERE SUBSTRING('+' + tb.f1, B.id, 1) = '+'
ORDER BY 1,2
GO
select f1=left(f1,charindex('+',f1)-1),f2
from tb
where charindex('+',f1)>0
union all
select f1=substring(f1,charindex('+',f1)+1,len(f1)-charindex('+',f1)),f2
from tb
where charindex('+',f1)>0
union all
select f1,f2
from tb
where charindex('+',f1)=0