Sql Server 使用游标实现列转行

将表中的数据进行“列转行”，如下：

1.原表中的数据

select id,userid,username from T1

 

执行结果:

 

2.使用游标实现“列转行”

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
--显示结果
SELECT id,userid,username FROM @temp

 

执行结果: