显示列的分拆的方法

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

--drop table ta

本文地址：http://www.45fan.com/a/question/72383.html