34,591
社区成员
发帖
与我相关
我的任务
分享
/*--------------------------------------------------------------------------
* 表格旋转/转置: 好像也叫交叉表
* 说明
不支持下列数据类型: 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