SQL SERVER 行转列 - 游标

准备数据

CREATE  TABLE  TestTable
( 
[Id]  [int]  IDENTITY(1 , 1)  NOT  NULL , 
[UserName]  [nvarchar](50)  NULL , 
[Subject]  [nvarchar](50)  NULL , 
[Source]  [numeric](18, 0)  NULL 
) 
ON  [PRIMARY]
GO

INSERT  INTO  TestTable ([UserName] , [Subject] , [Source])  
SELECT  N'张三' , N'语文' , 60 
UNION  ALL 
SELECT  N'李四' , N'数学' , 70 
UNION  ALL 
SELECT  N'王五' , N'英语' , 80 
UNION  ALL 
SELECT  N'王五' , N'数学' , 75 
UNION  ALL 
SELECT  N'王五' , N'语文' , 57 
UNION  ALL 
SELECT  N'李四' , N'语文' , 80 
UNION  ALL 
SELECT  N'张三' , N'英语' , 100
GO

静态行转列

select  UserName  姓名 ,
sum(case  Subject  when  '语文'  then  Source  else  0  end)  语文 ,
sum(case  Subject  when  '数学'  then  Source  else  0  end)  数学 ,
sum(case  Subject  when  '英语'  then  Source  else  0  end)  英语 
from  TestTable 
group  by  UserName

动态行转列

declare  @fusername  varchar(30)
declare  @fsubject  varchar(30)
declare  @fsource  int
declare  @sql  varchar(500)
--1.创建临时表
create  table  #tmp1(姓名  varchar(10))

--2.第一次循环：构建表格动态列
declare  mycursor  cursor  for 
select  [subject]  from  testtable group  by  [subject] 
open  mycursor 
fetch  next  from  mycursor  into  @fsubject
while (@@fetch_status=0) 
begin 
exec('alter  table  #tmp1  add  ['+@fsubject+']  int  not  null  default(0)')
fetch  next  from  mycursor  into  @fsubject
end 
close  mycursor 
DEALLOCATE  mycursor

--select  *  from  #tmp1

--3.插入唯一姓名
insert  into  #tmp1(姓名) 
select  [username]  from  testtable  group  by  [username]

--select  *  from  #tmp1

--4.第二次循环更新数据
declare  mycursor2  cursor  for 
select  username , [subject] , [Source]  from  testtable
open  mycursor2 
fetch  next  from  mycursor2  into  @fusername , @fsubject , @fsource
while (@@fetch_status=0) 
begin 
set  @sql= 'update  t1  set  ['+@fsubject+'] = '+convert(varchar(10) , @fsource)+'  from  #tmp1  t1  where  姓名='''+@fusername+'''  '
print  @sql
exec(@sql)
fetch  next  from  mycursor2  into  @fusername , @fsubject , @fsource
end 
close  mycursor2 
DEALLOCATE  mycursor2

select  *  from  #tmp1

truncate  table  #tmp1
drop  table  #tmp1

使用pivot行转列

select  * 
from
(
	select  UserName , [Subject] , [Source]  from  TestTable
)  test 
pivot 
(
	sum(Source)  for  Subject  in (语文 , 数学 , 英语)
)  pvt