代码改变世界

行列转换

2007-07-24 14:45  土星的狗狗  阅读(433)  评论(3编辑  收藏  举报

写了一天啊~想搞一个通用的行列转换,越写越头痛,SQL本来就不是我的强项,有没有高手顶一下啊!
/*
行列转换
字段名    1   2    3
类别 小说 散文 哲学
男性 38.0 18.9 16.2
女性 59.2 30.6 10.2

select top 2 * from test
exec UP_ROW2COLUMN 'test',3
*/
ALTER PROCEDURE UP_ROW2COLUMN
(
 @TABLENAME VARCHAR(30),
 @ROWCOUNT VARCHAR(10)
)
as
set nocount on
create table #tableTemp (字段名 varchar(20),信息1 varchar(20),信息2 varchar(20),信息3 varchar(20))
declare @index int
declare @length int 
declare @colname varchar(20)
declare @insertStr varchar(500)
declare @selectStr varchar(8000)
set @insertStr = 'insert into #tableTemp (字段名,信息1,信息2,信息3) '
set @selectStr = ''''
  SELECT @colname = COL_NAME(OBJECT_ID(@TABLENAME), convert(nvarchar(10),1)) 
  set @selectStr = @selectStr + 'union all SELECT top 1 '''+@colname+''' as 列名,convert(varchar(20),'+@colname+') as 信息1,'''' as 信息2,'''' as 信息3 from '+@TABLENAME+' '
end
set @selectStr = substring(@selectStr,11,len(@selectStr)-9) 
exec (@insertStr + @selectStr)

exec sp_columns @table_name = @TABLENAME
set @length = @@rowcount+1 
set @index = 2 
while (@index < @length) 
begin
 SELECT @colname = COL_NAME(OBJECT_ID(@TABLENAME), convert(nvarchar(10),@index)) 
 set @selectStr = 'SELECT top @index '''+@colname+''' as 列名,convert(varchar(20),'+@colname+') as 信息1,'''' as 信息2,'''' as 信息3 from '+@TABLENAME+'
 set @index = @index+1 
end
select * from #tableTemp
drop table #tabletemp

Creative Commons License
It's my life