plsql-----矩阵转置（转）

矩阵转置
有表 unknown
nam a c b d
--------------------------------------
Tom 1 2 3 4
Sun 5 6 7 8
mon 9 10 11 12
das 13 14 15 16
hor 17 18 19 20

要求以纵向格式显示
name col1 col2 col3 col4 col5
----------------------------------------------
nam tom sun mon das hor
a 1 5 9 13 17
c 2 6 10 14 18
b 3 7 11 15 19
d 4 8 12 16 20
方法一：使用循环。
create table test(nam varchar(4),a int,c int,b int,d int)
insert test(nam,a,c,b,d)
    select 'Tom',     1,     2,     3,     4
union all select 'Sun' ,    5 ,    6 ,    7 ,    8
union all select 'mon'  ,   9  ,   10 ,    11,     12
union all select 'das'   ,  13  ,   14 ,    15,     16
union all select 'hor'    , 17   ,  18  ,   19 ,    20

create proc proc_sky_blue (@tablename varchar(200))
as
begin
  set nocount on
  declare @col nvarchar(256)
  declare @makesql nvarchar(4000)
  declare @insertsql nvarchar(4000)
  declare @caculatesql nvarchar(400)
  declare @count int
  declare @i int
  create table #tmp (colname nvarchar(20))
  select @caculatesql = 'select @count=count(1) from ' + @tablename
  exec sp_executesql @caculatesql, N'@count int output',@count output
  if @count >=1024
  begin
    raiserror('表的行数太多了，我转不了',16,1)
  end
  else
  begin
    select @i=0
    while @count >0
    begin
      select @i=@i+1
      select @makesql = 'alter table #tmp add col'+convert(varchar(20),@i)+' int'
      exec(@makesql)
      select @count=@count-1
    end
    declare my_cursor cursor for
    select name from syscolumns where id=object_id(@tablename) order by colid
    open my_cursor
    fetch next from my_cursor into @col
    while @@fetch_status = 0
    begin
      select @makesql ='select @insertsql= @insertsql + convert(varchar(4),'+@col+') +'','' from ' +@tablename
      select @insertsql =N'insert #tmp values ('''+@col+ ''','
      execute sp_executesql @makesql,N'@insertsql nvarchar(4000) output' ,@insertsql output
      select @insertsql = left(@insertsql,len(@insertsql)-1) +')'
      exec(@insertsql)
      fetch next from my_cursor into @col
    end
    close my_cursor
    deallocate my_cursor
    select * from #tmp
    set nocount off
  end
end

exec proc_sky_blue 'test'

drop table test
drop proc proc_sky_blue
方法二：

--测试数据

create table test(nam varchar(4),a int,c int,b int,d int)
insert test(nam,a,c,b,d)
    select 'Tom',     1,     2,     3,     4
union all select 'Sun' ,    5 ,    6 ,    7 ,    8
union all select 'mon'  ,   9  ,   10 ,    11,     12
union all select 'das'   ,  13  ,   14 ,    15,     16
union all select 'hor'    , 17   ,  18  ,   19 ,    20
union all select 'Jun'  ,   9  ,   10 ,    11,     12
union all select 'Feb'   ,  13  ,   14 ,    15,     16
union all select 'Mar'    , 17   ,  18  ,   19 ,    20
union all select 'Apr'  ,   9  ,   10 ,    11,     12
union all select 'May'   ,  13  ,   14 ,    15,     16
union all select 'Jun'    , 17   ,  18  ,   19 ,    20
union all select 'Jul'  ,   9  ,   10 ,    11,     12
union all select 'Aug'   ,  13  ,   14 ,    15,     16
union all select 'Sep'    , 17   ,  18  ,   19 ,    20
union all select 'Oct'  ,   9  ,   10 ,    11,     12
union all select 'Nov'   ,  13  ,   14 ,    15,     16
union all select 'Dec'    , 17   ,  18  ,   19 ,    20

--查询处理
declare @s1 varchar(8000),@s2 varchar(8000)
 ,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
 ,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(800)'
 ,@s2=@s2+',@'+@i+'='''''
 ,@s3=@s3+'
select @'+@i+'=@'+@i+'+'',[''+nam+'']=''+cast(['+name+'] as varchar) from test'
 ,@s4=@s4+',@'+@i+'=''select ''+substring(@'+@i+',2,8000)'
 ,@s5=@s5+'+'' union all ''+@'+@i
 ,@i=cast(@i as int)+1
from syscolumns
where object_id('test')=id and colid<>1

select @s1=substring(@s1,2,8000)
 ,@s2=substring(@s2,2,8000)
 ,@s4=substring(@s4,2,8000)
 ,@s5=substring(@s5,16,8000)
select @s1,@s2,@s3,@s4,@s5

exec('declare '+@s1+'
select '+@s2+@s3+'
select '+@s4+'
exec('+@s5+')')
go

--删除测试表
drop table test

解读：N列数据用N-1个变量来保存N-1列的数据列表，其中第一列作为字段。
此方法灵活地应用了动态语句的特点，其大致思路是一次取得一列数据的值。
即展开后s3的值为：
select @0=@0+',['+nam+']='+cast([a] as varchar) from test
select @1=@1+',['+nam+']='+cast([c] as varchar) from test
select @2=@2+',['+nam+']='+cast([b] as varchar) from test
select @3=@3+',['+nam+']='+cast([d] as varchar) from test
缺点：受被转换的数据行行数限制，即上面的@0,@1,@2,@3最大只能容纳8000个字符
转换后的行不能超过1024行，这是数据库的限制。

 

本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/lee576/archive/2006/12/11/1438973.aspx