怎样一行分成几行?

Question

表AIText
1,2
3,4,5查询结果如下:IText
1
2
3
4
5 
怎么写sql

Answer 1

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 行）

Answer 2

create table ta( IText varchar(50))
insert ta select '1,2'
union all select '3,4,5'declare @tb table(IText varchar(50))--用表变量显示效果
DECLARE @IText varchar(50)
DECLARE roy CURSOR
FOR SELECT IText from ta
OPEN roy
FETCH next FROM roy
into @IText
WHILE @@FETCH_STATUS = 0
BEGIN
    while CHARINDEX(',',@IText)>0 
        begin
            INSERT   @tb  select LEFT(@IText,CHARINDEX(',',@IText)-1) 
            SET      @IText=STUFF(@IText,1,CHARINDEX(',',@IText),'')   
        end
        insert @tb select @IText
        FETCH NEXT FROM roy into @IText
end
CLOSE roy
DEALLOCATE roy
select * from @tbIText                                              
-------------------------------------------------- 
1
2
3
4
5（所影响的行数为 5 行）

Answer 3

借助臨時表，在效率上更優
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 BSelect 
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 ITextDrop Table #T
GO
Drop Table A
--Result
/*
IText
1
2
3
4
5
*/

Answer 4

如果數據沒有那麼多，可以將臨時表的數據量改小點Create Table A
(IText Varchar(100))
Insert A Select '1,2'
Union All Select '3,4,5'
GO
Select Top 1000 ID = Identity(Int, 1, 1) Into #T From Syscolumns A, Syscolumns BSelect 
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 ITextDrop Table #T
GO
Drop Table A
--Result
/*
IText
1
2
3
4
5
*/