SQL 表数据旋转90度(二维转换)

/**//*
将表数据旋转90度(2007-11-19于海南三亚)

将下表数据：
A                    b           c           d           e           
-------------------- ----------- ----------- ----------- ----------- 
x                    1           2           3           4
y                    5           6           7           8
z                    9           10          11          12

转化成如下结果：
a                    x          y          z          
-------------------- ---------- ---------- ---------- 
b                    1          5          9
c                    2          6          10
d                    3          7          11
e                    4          8          12

*/

--生成测试数据
create table test1(A varchar(20),b int,c int,d int,e int)
insert into test1 select 'x',1,2 ,3 ,4
insert into test1 select 'y',5,6 ,7 ,8
insert into test1 select 'z',9,10,11,12
go

--生成中间数据表
declare @s varchar(8000)
set @s = 'create table test2(a varchar(20)'
select @s = @s + ',' + A + ' varchar(10)' from test1
set @s = @s + ')'
exec(@s)
print @s
--借助中间表实现行列转换
declare @name varchar(20)

declare t_cursor cursor for 
select name from syscolumns 
where id=object_id('test1') and colid > 1 order by colid

open t_cursor

fetch next from t_cursor into @name

while @@fetch_status = 0
begin
    exec('select ' + @name + ' as t into test3 from test1')
    set @s='insert into test2 select ''' + @name + ''''
    select @s = @s + ',''' + rtrim(t) + '''' from test3
    exec(@s)
    exec('drop table test3')
    fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor

--查看行列互换处理结果
select * from test1
select * from test2

--删除表
drop table test1
drop table test2
----------------------------------------------------------------------------
/**//*固定的写法:*/
select t1.* , t2.y , t3.z from
(select a = 'b' , x = b from test1 where a = 'x') t1, 
(select a = 'b' , y = b from test1 where a = 'y') t2,
(select a = 'b' , z = b from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'c' , x = c from test1 where a = 'x') t1, 
(select a = 'c' , y = c from test1 where a = 'y') t2,
(select a = 'c' , z = c from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'd' , x = d from test1 where a = 'x') t1, 
(select a = 'd' , y = d from test1 where a = 'y') t2,
(select a = 'd' , z = d from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'e' , x = e from test1 where a = 'x') t1, 
(select a = 'e' , y = e from test1 where a = 'y') t2,
(select a = 'e' , z = e from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a

----------------------------------------------------------------------------
/**//*
表tb,数据如下:
项目种类  业绩  提成
洗吹类　　200   10
外卖      100   5
合计      300   15
转换成:
项目种类  洗吹类  外卖  合计
业绩      200     100   300
提成      10      5     15
*/

create table tb
(
  项目种类 varchar(10),
  业绩     int,
  提成     int
)

insert into tb(项目种类,业绩,提成) values('洗吹类',200,10)
insert into tb(项目种类,业绩,提成) values('外卖'  ,100,5)
insert into tb(项目种类,业绩,提成) values('合计'  ,300,15)
go

select 项目种类,sum(洗吹类) as 洗吹类 , sum(外卖) as 外卖 , sum(合计) as 合计 from
(
  select 项目种类 = '业绩',
         洗吹类   = case when 项目种类 = '洗吹类' then 业绩 else 0 end,
         外卖     = case when 项目种类 = '外卖'   then 业绩 else 0 end,
         合计     = case when 项目种类 = '合计'   then 业绩 else 0 end
  from tb
union all
  select 项目种类 = '提成' ,
         洗吹类   = case when 项目种类 = '洗吹类' then 提成 else 0 end,
         外卖     = case when 项目种类 = '外卖'   then 提成 else 0 end,
         合计     = case when 项目种类 = '合计'   then 提成 else 0 end
  from tb
) m
group by 项目种类
order by 项目种类 desc

drop table tb

/**//*
项目种类 洗吹类      外卖        合计          
-------- ----------- ----------- ----------- 
业绩     200         100         300
提成     10          5           15

（所影响的行数为 2 行）
*/

--------------------------------------------------------------------------
/**//*
数据库中tb表格如下

月份    工资   福利  奖金
月     100    200   300
月     110    210   310
月     120    220   320
月     130    230   330

我想得到的结果是

项目   1月    2月  3月  4月
工资   100    110  120  130
福利   200    210  220  230
奖金   300    310  320  330

就是说完全把表格的行列颠倒，有点像那种旋转矩阵，请问如何用sql 语句实现？
*/

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_zj]
GO
/**//*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/

create proc p_zj
       @tbname sysname, --要处理的表名
       @fdname sysname, --做为转换的列名
       @new_fdname sysname='' --为转换后的列指定列名
as
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(8000)',
       @s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''
       else @new_fdname + '=' end + '''''' + name + '''''''',
       @s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname + 
       ']+'']=''+cast([' + name + '] as varchar) from [' + @tbname + ']',
       @s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,
       @s5 = @s5 + '+'' union all ''+@' + @i,
       @i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name<>@fdname

select @s1=substring(@s1,2,8000),
       @s2=substring(@s2,2,8000),
       @s4=substring(@s4,2,8000),
       @s5=substring(@s5,16,8000)
exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '
exec(' + @s5 + ')')
go

--用上面的存储过程测试：

create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test 
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
go

exec p_zj 'Test', '月份' , '项目'

drop table Test
drop proc p_zj

/**//*
项目   1月         2月         3月         4月          
---- ----------- ----------- ----------- ----------- 
福利   200         210         220         230
工资   100         110         120         130
奖金   300         310         320         330

（所影响的行数为 3 行）
*/

/**//*
静态写法(SQL2005)
*/
--测试环境
create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
go
--测试语句
SELECT * FROM 
(
  SELECT 考核月份,月份,金额 FROM 
     (SELECT 月份, 工资, 福利, 奖金 FROM Test) p
  UNPIVOT
     (金额 FOR 考核月份 IN (工资, 福利, 奖金))AS unpvt
) T
PIVOT
(MAX(金额)  FOR 月份 in ([1月],[2月],[3月],[4月]))AS pt

--测试结果

/**//*
考核月份  1月     2月      3月     4月
-------  -----  -----   ------  -------
福利200210220230
工资100110120130
奖金300310320330
*/

--删除环境
Drop table Test