怎样一行分成几行?
表A

IText
1,2
3,4,5

查询结果如下:

IText
1
2
3
4
5  
怎么写sql

------解决方案--------------------
create table ta(id int, name varchar(50))
insert ta
select 1, 'aa,bb '
union all select 2, 'aaa,bbb,ccc '
union all select 3, 'Aa,Bb,Cc,Dd '


方法1通过游标实现:

declare @tb table(id int, name varchar(50))--用表变量显示效果
DECLARE @id int,@name varchar(50)
DECLARE roy CURSOR
FOR SELECT * from ta
OPEN roy
FETCH next FROM roy
into @id,@name
WHILE @@FETCH_STATUS = 0
BEGIN
while CHARINDEX( ', ',@name)> 0
begin
INSERT @tb select @id,LEFT(@name,CHARINDEX( ', ',@name)-1)
SET @name=STUFF(@name,1,CHARINDEX( ', ',@name), ' ')
end
insert @tb select @id,@name
FETCH NEXT FROM roy into @id,@name
end
CLOSE roy
DEALLOCATE roy
select * from @tb

方法2用表变量:
declare @ta table(id int)--生成1—50递增的表变量
declare @i int,@j int
select @i=1,@j=50--定义字符的最大数量
while @i!> @j
begin
insert @ta select @i
select @i=@i+1
end
select a.id,
显示列=substring(a.name,b.id,charindex( ', ',a.name+ ', ',b.id)-b.id)
from ta a,@ta b
where substring( ', '+a.name,b.id,1)= ', '

效果如下:


id name
----------- ------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
3 Aa
3 Bb
3 Cc
3 Dd

（所影响的行数为 9 行）


------解决方案--------------------
借助臨時表，在效率上更優


Create Table A
(IText Varchar(100))
Insert A Select '1,2 '
Union All Select '3,4,5 '
GO
Select Top 8000 ID = Identity(Int, 1, 1) Into #T From Syscolumns A, Syscolumns B

Select
IText = Substring(A.IText, B.ID, CharIndex( ', ', A.IText + ', ', B.ID) - B.ID)
From A, #T B
Where Substring( ', ' + a.IText, B.id, 1) = ', '
Order By IText

Drop Table #T
GO
Drop Table A
--Result
/*
IText
1
2
3
4
5
*/