Sql Server 行转列及游标使用

 

先建立一个数据表 StudentTable

CREATE TABLE StudentTable(
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [UserName] [nvarchar](50) NULL,
 [Subname] [nvarchar](50) NULL,
 [Source] [numeric](18, 0) NULL
) ON [PRIMARY]
go
 INSERT INTO StudentTable ([UserName],[Subname],[Source]) 
 SELECT N'张三',N'语文',65 UNION ALL
 SELECT N'李四',N'数学',75 UNION ALL
 SELECT N'王五',N'英语',80 UNION ALL
 SELECT N'王五',N'数学',78 UNION ALL
 SELECT N'王五',N'语文',48 UNION ALL
 SELECT N'李四',N'语文',82 UNION ALL
 SELECT N'张三',N'英语',100
GO

 

然后建立一个存储过程，对数据进行转换

alter proc pro_test
as

  declare @subjName  varchar(50);
  declare @sql varchar(2000);

  select id,[UserName],[Subname],[Source] into #objTab from TestTable ;
  select DIstinct UserName  into #studentTab from #objTab;
    set @subjName='';
    Declare  chimayoubiao  Cursor for select distinct Subname from  #objTab  for read only;
    open chimayoubiao
    fetch next from  chimayoubiao into @subjName;

    while @@FETCH_STATUS =0 
    begin
       set @sql='alter table #studentTab add '+ @subjName +' varchar(50) default(0);';
       exec(@sql);
       print @sql;
       set @sql='update #studentTab set '+@subjName+'= Q.Source 
       from #studentTab C join #objTab Q on c.UserName=Q.UserName
       and '''+@subjName+''' = Q.Subname';
       Exec(@sql);
       print @sql;
       set @sql=' update #studentTab set '+ @subjName +'=0 where '+ @subjName +' is null';
       Exec(@sql);
       fetch next from chimayoubiao into @subjName;
    end;


select * from #studentTab;
close chimayoubiao;
deallocate chimayoubiao

drop table #studentTab;
drop table #objTab;

go

 

 

使用pivot 进行转换

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