遇到一个问题解决不了，求大家帮忙

[lakerskobemvp]

一个表的两列都是通过字符分隔的，现在想把他们分开一一对应，例如
，
求大家帮忙

[等不到来世]

select a.a,b.b
from
(select a=SUBSTRING(t2.a,charindex(',',','+t2.a,t1.number),charindex(',',t2.a+',',t1.number)-t1.number),t1.number
from master..spt_values t1, tb t2
where t1.type='P' and charindex(',',','+t2.a,t1.number)=t1.number) a
join
(select b=SUBSTRING(t2.b,charindex(',',','+t2.b,t1.number),charindex(',',t2.b+',',t1.number)-t1.number),t1.number
from master..spt_values t1, tb t2
where t1.type='P' and charindex(',',','+t2.b,t1.number)=t1.number) b
on a.number=b.number

/*
a	b
---------
1	a
2	b
3	c
*/

[Pact_Alice]

CREATE TABLE # ( A VARCHAR(30), B VARCHAR(30) ) INSERT INTO # SELECT '1,2,3','a,b,c' CREATE TABLE #t4 ( A VARCHAR(30), B VARCHAR(30) ) --定义变量 DECLARE @sql VARCHAR(30), @sql1 VARCHAR(30), @i INT --给变量赋值 SET @sql=(SELECT A + ',' FROM #) set @sql1=( SELECT B + ',' FROM # ) SET @i =(SELECT COUNT(*) FROM #) WHILE Charindex(',', @sql) > 0 BEGIN -- INSERT INTO #t4 SELECT LEFT(@sql, Charindex(',', @sql) - 1), LEFT(@sql1, Charindex(',', @sql1) - 1) SET @sql= RIGHT(@sql, Len(@sql) - Charindex(',', @sql)) SET @sql1= RIGHT(@sql1, Len(@sql1) - Charindex(',', @sql1)) END