Sql Server使用游标实现列转行
将表中的数据进行“列转行”,如下:
1.原表中的数据
Sql代码
select id,userid,username from T1
www.2cto.com
执行结果:
![](http://www.2cto.com/uploadfile/2012/1020/20121020102812580.jpg)
2.使用游标实现“列转行”
Sql代码
DECLARE @temp TABLE(id int,userid varchar(50),username varchar(50))
--定义游标并进行处理
DECLARE mycursor CURSOR LOCAL
FOR
select id,userid,username from T1
DECLARE @id int,@userid varchar(100),@username varchar(100)
OPEN mycursor
FETCH mycursor INTO @id,@userid,@username
WHILE @@FETCH_STATUS=0
BEGIN
while(charindex(',',@username)<>0)
begin
insert @temp(id,userid,username) values (@id,substring(@userid,1,charindex(',',@userid)-1),substring(@username,1,charindex(',',@username)-1))
set @userid = stuff(@userid,1,charindex(',',@userid),'')
set @username = stuff(@username,1,charindex(',',@username),'')
end
insert @temp(id,userid,username) values (@id,@userid,@username)
FETCH mycursor INTO @id,@userid,@username
END
CLOSE mycursor
DEALLOCATE mycursor
www.2cto.com
--显示结果
SELECT id,userid,username FROM @temp
执行结果:
![](http://www.2cto.com/uploadfile/2012/1020/20121020102812792.jpg)