/*--------------------------------------

* 表格旋转/转置: 好像也叫交叉表

* 说明

  不支持下列数据类型: 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   -- 日期时间转换样式
)
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