SQL列转行的问题

declare @t table(a int,b int)

insert into @t select 1,2

insert into @t select 3,4

insert into @t select 5,6



select id=identity(int,1,1),* into # from @t





select max(case when num=1 then a end),

max(case when num=2 then a end),

max(case when num=3 then a end) 

from 

(

select *,case when id%3=0 then 3 else id%3 end as num from #

) aa

union all

select max(case when num=1 then b end),

max(case when num=2 then b end),

max(case when num=3 then b end) 

from 

(

select *,case when id%3=0 then 3 else id%3 end as num from #

) aa

create table test(city varchar(10), temp varchar(10)) 

insert test select  

'广州','热' union select  

'上海','冷'  union select 

'深圳','凉快' 

go 



declare @s varchar(8000)  

select @s= isnull(@s+',','')+'['+city+']=max(case when city = '''+city+''' then temp else null end)'  

from test  



exec('select '+ @s + ' from test ') 



drop table test  



/* 



广州         上海         深圳          

---------- ---------- ----------  

热          冷          凉快 



警告: 聚合或其它 SET 操作消除了空值。 

*/

declare @t table(a int,b int)

insert into @t select 1,2

insert into @t select 3,4

insert into @t select 5,6



select id=identity(int,1,1),* into # from @t



select max(case id when '1' then a else '' end),

max(case id when '2' then a else '' end),

max(case id when '3' then a else '' end)

from #

union all

select max(case id when '1' then b else '' end),

max(case id when '2' then b else '' end),

max(case id when '3' then b else '' end)

from #

/*

将表数据旋转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表格如下

 

月份    工资   福利  奖金

1月     100    200   300

2月     110    210   310

3月     120    220   320

4月     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