表格旋转/转置/交叉表,该怎么处理

/*--------------------------------------* 表格旋转/转置: 好像也叫交叉表* 说明  不支持下列数据类型: image, text, ntext, hierarchyid, geometry, geography.  2000 好像没有 fn_varbintohexstr, 所以不支持 varbinary, binary, timestamp,  要扩展自己写 bin2str 函数.  2008 的 varbinary/binary 可以直接 convert, 详细参考联机/MSDN, 但懒得判断  版本了, 一律用 fn_varbintohexstr.  2000 内层受长度 8000 的限制, 某列数据超过 8000 长度肯定报错.  2005/2008 检测用 varchar(max)  轴向列转 sysname, 超过 128 截断. 其它列除有限的几个要显式转换的数据类型,  一律用 rtrim() 隐式转换, 具体看代码.  有处理 NULL 值, 不至于被一个 NULL 玩死.* 作者  这些东西毫无技术含量可言, 不敢言称作者, 以免贻笑大方.  PS: 写着玩/不维护/不扩展, BUG 有时间就跟进无时间见谅. 相信除了长度限制的  硬伤, 其它 BUG 的可能性不大.--------------------------------------*/CREATE PROCEDURE p_rotate(    @table  sysname,        -- 表/视图    @axis   sysname = null, -- 轴, 旋转后作为字段名, 默认第1列    @rename sysname = null, -- 重命名轴    @style  int     = 121   -- 日期时间转换样式)ASSET NOCOUNT ONif 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 alldeclare @max   varchar(10)declare @type  intselect @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 [email protected]if @type in (34,35,99,240) -- image,text,ntext,hierarchyid,geometry,geographyor @@version not like '%Server 200[58]%' and @type in (165,173,189) -- varbinary,binary,timestampbegin    select name from systypes where xtype = @type    returnendselect @rename = isnull(@rename, @axis), @max = case when @@version like '%Server 200[58]%' then 'max' else '8000' end-- 构造内层 execselect    @inner = isnull(@inner+',','')+'@'+ltrim(colid)+' varchar([email protected]+')',    @first = isnull(@first+',','')+'@'+ltrim(colid)+'=''select [[email protected]+']='''''+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),[[email protected]+']))' -- timestamp            when @type in (165,173) then 'left(master.sys.fn_varbintohexstr([[email protected]+']),128)' -- varbinary,binary            when @type in (175,239) then 'rtrim(convert(sysname,[[email protected]+']))' -- char,nchar            when @type in (40,41,42,43,58,61) then 'convert(sysname,[[email protected]+'],'+ltrim(@style)+')' -- date,time,datetime2,datetimeoffset,smalldatetime,datetime            else 'convert(sysname,[[email protected]+'])'        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([email protected]+'),['+name+'])' -- sql_variant,xml            else 'rtrim(['+name+'])'        end+', char(39)),''null'') from [[email protected]+']',    @union = isnull(@union+'+'' union all ''+','')+'@'+ltrim(colid)from syscolumnswhere 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/execexec('declare [email protected]+'select [email protected][email protected]+'exec([email protected]+')')SET NOCOUNT OFF