[原创阿原创]SQL server 用存储过程实现表内容的转置

use [HR数据库]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE Transform
--参数 筛选的工号
 ( @EmpID varchar(20)
)
AS
BEGIN
 SET NOCOUNT ON;
 --获得相应工号的数据量 即新表的列数
 DECLARE @ColumnCount int
    select @ColumnCount=count(*) from [dbo].[Data_面试] where [工号]=@EmpID
 
 --构造建立新表[TranformedTable]的代码,列名为columnN
 DECLARE @NewTableCode varchar(1000)
 set @NewTableCode='create table [dbo].[TranformedTable] ('
 
 declare @i int
 set   @i=0  
   
 while   @i<@ColumnCount  
 begin  
  set @NewTableCode=@NewTableCode+ 'column' + convert(char,(@i+1)) + ' varchar(100),'
  set @i=@i+1  
 end
 set @NewTableCode=@NewTableCode + ')'

 IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TranformedTable]'))
  DROP table [dbo].[TranformedTable]
 
 exec(@NewTableCode)

 --新表[TranformedTable]建立完毕

 --这里开始得到原表的列数，也就是新表的行数
 declare @NewCount int
 select @NewCount=count(name) from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='Data_面试')
 --原表的列名
 declare @ColumnName varchar(100) 
 --@j 循环量，读取每一个列的数据然后插入到新表[TranformedTable]
 declare @j int
 set @j=1
 while @j<@NewCount+1
 begin
  select @ColumnName=col_name(object_id('Data_面试'),@j) from  sysobjects
  
  --判断列的类型 如是日期的则转换成varchar,YYYY/MM/DD
  declare @ColumnType varchar(100)
  select @ColumnType=b.name from syscolumns a,systypes b  
   where a.name=@ColumnName and a.xtype=b.xtype
  if(@ColumnType='smalldatetime')
  begin
   set @ColumnName='convert(varchar,[' + @ColumnName + '],111)'
  end
  --获得此列名的数据 
  declare @r varchar(100)
  SET @r = ''    
  
  declare @tempsql varchar(200)
  IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[table_temp]'))
  DROP table [dbo].[table_temp]
  set @tempsql='select ' + @ColumnName + ' as [newcol] into [table_temp] from [Data_面试] where [工号]=''' +  @EmpID + ''''
  exec(@tempsql)
  
  SELECT  @r =@r + ',''' + cast([newcol] as varchar) + '''' from table_temp  
  set @r=STUFF(@r, 1, 1, '')  
  exec('insert into [dbo].[TranformedTable] values(' + @r + ')')  
  DROP table [dbo].[table_temp]
  set @j=@j+1
 end
 
 select * from [TranformedTable]
END
GO

exec Transform. @EmpID=N'2007000595'