如何转换以下的EXCEL表格

CREATE PROCEDURE p_rotate

(

    @table  sysname,        -- 表/视图

    @axis   sysname = null, -- 轴, 旋转后作为字段名, 默认第1列

    @rename sysname = null, -- 重命名轴

    @style  int     = 121   -- 日期时间转换样式

)

AS



SET NOCOUNT ON



if object_id(@table) is null return -- 不废话



declare @inner varchar(8000) -- 定义内层 exec 变量

declare @first varchar(8000) -- 每行数据的第一列 即原字段名变成第1列

declare @rows  varchar(8000) -- 读取每列数据作为行数据

declare @union varchar(8000) -- 每行数据 union all

declare @max   varchar(10)

declare @type  int



select @axis = isnull(@axis, (select name from syscolumns where id=object_id(@table) and colid=1))

select @type = xtype from syscolumns where id=object_id(@table) and name=@axis



if @type in (34,35,99,240) -- image,text,ntext,hierarchyid,geometry,geography

or @@version not like '%Server 200[58]%' and @type in (165,173,189) -- varbinary,binary,timestamp

begin

    select name from systypes where xtype = @type

    return

end



select @rename = isnull(@rename, @axis), @max = case when @@version like '%Server 200[58]%' then 'max' else '8000' end



-- 构造内层 exec

select

    @inner = isnull(@inner+',','')+'@'+ltrim(colid)+' varchar('+@max+')',

    @first = isnull(@first+',','')+'@'+ltrim(colid)+'=''select ['+@rename+']='''''+name+'''''''',

    @rows = isnull(@rows,'')+char(13)+char(10)+'select @'+ltrim(colid)+'=@'+ltrim(colid)+'+'',[''+isnull('+

        case

            when @type = 189 then 'master.sys.fn_varbintohexstr(convert(binary(8),['+@axis+']))' -- timestamp

            when @type in (165,173) then 'left(master.sys.fn_varbintohexstr(['+@axis+']),128)' -- varbinary,binary

            when @type in (175,239) then 'rtrim(convert(sysname,['+@axis+']))' -- char,nchar

            when @type in (40,41,42,43,58,61) then 'convert(sysname,['+@axis+'],'+ltrim(@style)+')' -- date,time,datetime2,datetimeoffset,smalldatetime,datetime

            else 'convert(sysname,['+@axis+'])'

        end+',''NULL'')+'']=''+isnull(quotename('+

        case

            when xtype = 189 then 'master.sys.fn_varbintohexstr(convert(binary(8),['+name+']))' -- timestamp

            when xtype in (165,173) then 'master.sys.fn_varbintohexstr(['+name+'])' -- varbinary,binary

            --when xtype in (60,122) then 'convert(varchar(50),['+name+'],2)' -- money,smallmoney -- 需要精细控制类型转换这里添加

            when xtype in (40,41,42,43,58,61) then 'convert(varchar(50),['+name+'],'+ltrim(@style)+')' -- date,time,datetime2,datetimeoffset,smalldatetime,datetime

            when xtype in (98,241) then 'convert(varchar('+@max+'),['+name+'])' -- sql_variant,xml

            else 'rtrim(['+name+'])'

        end+', char(39)),''null'') from ['+@table+']',

    @union = isnull(@union+'+'' union all ''+','')+'@'+ltrim(colid)

from syscolumns

where id=object_id(@table) and name<>@axis and (xtype not in (34,35,99,165,173,189,240) or @@version like '%Server 200[58]%' and xtype not in (34,35,99,240))

order by colid



-- print/exec

exec('declare '+@inner+'

select '+@first+@rows+'

exec('+@union+')')



SET NOCOUNT OFF

--> 测试数据：student

if object_id('student') is not null drop table student

create table student(姓名 varchar(8), 学号 int, 专业 varchar(8), 性别 varchar(8), 高数 int, 化学 int, 英语 int, 物理 int, 总分 float, 个人平均分 float, 名次 int)

insert into student

select '学生壬', 1009, '热能', '女', 89, 93, 84, 90, 356.00, 89.00, 1 union all

select '学生甲', 1001, '冶金', '男', 88, 87, 78, 98, 351.00, 87.75, 2 union all

select '学生癸', 1010, '热能', '女', 83, 91, 85, 89, 348.00, 87.00, 3 union all

select '学生丙', 1003, '冶金', '女', 97, 90, 70, 89, 346.00, 86.50, 4 union all

select '学生戊', 1005, '冶金', '男', 91, 99, 69, 81, 340.00, 85.00, 5 union all

select '学生寅', 1013, '机械', '女', 90, 80, 83, 76, 329.00, 82.25, 6 union all

select '学生卯', 1014, '机械', '男', 81, 92, 88, 62, 323.00, 80.75, 7 union all

select '学生辛', 1008, '热能', '女', 70, 80, 80, 84, 314.00, 78.50, 8 union all

select '学生辰', 1015, '机械', '男', 83, 91, 74, 65, 313.00, 78.25, 9 union all

select '学生丁', 1004, '冶金', '女', 79, 69, 83, 78, 309.00, 77.25, 10 union all

select '学生丑', 1012, '机械', '男', 92, 70, 77, 60, 299.00, 74.75, 11 union all

select '学生庚', 1007, '热能', '男', 76, 86, 59, 74, 295.00, 73.75, 12 union all

select '学生子', 1011, '机械', '男', 69, 84, 71, 71, 295.00, 73.75, 13 union all

select '学生乙', 1002, '冶金', '男', 85, 79, 72, 57, 293.00, 73.25, 14 union all

select '学生己', 1006, '热能', '男', 85, 73, 66, 69, 293.00, 73.25, 15



select * from student

-- 转置

exec p_rotate 'student'

(15 行受影响)



(15 行受影响)

消息 2812，级别 16，状态 62，第 23 行

找不到存储过程 'p_rotate'。