求教一个简单的sql 多列转多行 的有关问题

贴了算了/*将表数据旋转90度(2007-11-19于海南三亚)将下表数据：A                    b           c           d           e           -------------------- ----------- ----------- ----------- ----------- x                    1           2           3           4y                    5           6           7           8z                    9           10          11          12转化成如下结果：a                    x          y          z          -------------------- ---------- ---------- ---------- b                    1          5          9c                    2          6          10d                    3          7          11e                    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 ,4insert into test1 select 'y',5,6 ,7 ,8insert into test1 select 'z',9,10,11,12go--生成中间数据表declare @s varchar(8000)set @s = 'create table test2(a varchar(20)'select @s = @s + ',' + A + ' varchar(10)' from test1set @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 colidopen t_cursorfetch next from t_cursor into @namewhile @@fetch_status = 0begin    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 @nameendclose t_cursordeallocate t_cursor--查看行列互换处理结果select * from test1select * from test2--删除表drop table test1drop 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') t3where t1.a = t2.a and t1.a = t2.aunion allselect 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') t3where t1.a = t2.a and t1.a = t2.aunion allselect 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') t3where t1.a = t2.a and t1.a = t2.aunion allselect 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') t3where 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)goselect 项目种类,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 tbunion all  select 项目种类 = '提成' ,         洗吹类   = case when 项目种类 = '洗吹类' then 提成 else 0 end,         外卖     = case when 项目种类 = '外卖'   then 提成 else 0 end,         合计     = case when 项目种类 = '合计'   then 提成 else 0 end  from tb) mgroup by 项目种类order by 项目种类 descdrop table tb/*项目种类 洗吹类      外卖        合计          -------- ----------- ----------- ----------- 业绩     200         100         300提成     10          5           15（所影响的行数为 2 行）*/--------------------------------------/*数据库中tb表格如下 月份    工资   福利  奖金1月     100    200   3002月     110    210   3103月     120    220   3204月     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.sysobjectswhere 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='' --为转换后的列指定列名asdeclare @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)+1from syscolumnswhere object_id(@tbname)=id and name<>@fdnameselect @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 allselect '2月',110,210,310 union allselect '3月',120,220,320 union allselect '4月',130,230,330goexec p_zj 'Test', '月份' , '项目'drop table Testdrop 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 Testselect '1月',100,200,300 union allselect '2月',110,210,310 union allselect '3月',120,220,320 union allselect '4月',130,230,330go--测试语句SELECT * FROM (  SELECT 考核月份,月份,金额 FROM      (SELECT 月份, 工资, 福利, 奖金 FROM Test) p  UNPIVOT     (金额 FOR 考核月份 IN (工资, 福利, 奖金))AS unpvt) TPIVOT(MAX(金额)  FOR 月份 in ([1月],[2月],[3月],[4月]))AS pt--测试结果/*考核月份  1月     2月      3月     4月-------  -----  -----   ------  -------福利200210220230工资100110120130奖金300310320330*/--删除环境Drop table Test