将
1, '1,2' , 'a,b'
2 , '1,3' , 'a,c,d'
拆成
1,1,a
1,1,b
1,2,a
1,2,b
2,1,a
2,1,c
2,1,d
2,3,a
2,3,c
2,3,d
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( id int, code varchar(10),code2 varchar(10))
go
insert tb SELECT
1, '1,2' , 'a,b' UNION ALL SELECT
2 , '1,3' , 'a,c,d'
go
select a.id,code,code2=substring(a.code2,b.number,charindex(',',a.code2+',',b.number)-b.number)
from (
Select
a.id,code=substring(a.code,b.number,charindex(',',a.code+',',b.number)-b.number) ,code2
from
Tb a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.code)
where
substring(','+a.code,b.number,1)=',') a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.code2)
where
substring(','+a.code2,b.number,1)=','
go