直接写死吧.想不出好办法. SQL> select '指标项'COLUMN_NAME, max(TO_NUMBER(case when a.month='201008' THEN a.month end)) month1, 2 max(TO_NUMBER(case when a.month='201009' THEN a.month end)) month2, 3 max(TO_NUMBER(case when a.month='201009' THEN a.month end)) month3 4 from temp a 5 union all 6 select 'rk_zl' COLUMN_NAME, max(case when a.month='201008' THEN a.RK_ZL end) month1, 7 max(case when a.month='201009' THEN a.RK_ZL end) month2, 8 max(case when a.month='201009' THEN a.RK_ZL end) month3 9 from temp a 10 union all 11 select 'fw_zl' COLUMN_NAME, max(case when a.month='201008' THEN a.FW_ZL end) month1, 12 max(case when a.month='201009' THEN a.FW_ZL end) month2, 13 max(case when a.month='201009' THEN a.FW_ZL end) month3 14 from temp a 15 union all 16 select 'zzl' COLUMN_NAME, max(case when a.month='201008' THEN a.FW_ZL end) month1, 17 max(case when a.month='201009' THEN a.FW_ZL end) month2, 18 max(case when a.month='201009' THEN a.FW_ZL end) month3 19 from temp a 20 ;
上面最后一行数据有问题. SQL> select '指标项'COLUMN_NAME, max(TO_NUMBER(case when a.month='201008' THEN a.month end)) month1, 2 max(TO_NUMBER(case when a.month='201009' THEN a.month end)) month2, 3 max(TO_NUMBER(case when a.month='201009' THEN a.month end)) month3 4 from temp a 5 union all 6 select 'rk_zl' COLUMN_NAME, max(case when a.month='201008' THEN a.RK_ZL end) month1, 7 max(case when a.month='201009' THEN a.RK_ZL end) month2, 8 max(case when a.month='201009' THEN a.RK_ZL end) month3 9 from temp a 10 union all 11 select 'fw_zl' COLUMN_NAME, max(case when a.month='201008' THEN a.FW_ZL end) month1, 12 max(case when a.month='201009' THEN a.FW_ZL end) month2, 13 max(case when a.month='201009' THEN a.FW_ZL end) month3 14 from temp a 15 union all 16 select 'zzl' COLUMN_NAME, max(case when a.month='201008' THEN a.zzl end) month1, 17 max(case when a.month='201009' THEN a.zzl end) month2, 18 max(case when a.month='201009' THEN a.zzl end) month3 19 from temp a 20 ;
SQL> select '指标项'COLUMN_NAME, max(TO_NUMBER(case when a.month='201008' THEN a.month end)) month1,
2 max(TO_NUMBER(case when a.month='201009' THEN a.month end)) month2,
3 max(TO_NUMBER(case when a.month='201009' THEN a.month end)) month3
4 from temp a
5 union all
6 select 'rk_zl' COLUMN_NAME, max(case when a.month='201008' THEN a.RK_ZL end) month1,
7 max(case when a.month='201009' THEN a.RK_ZL end) month2,
8 max(case when a.month='201009' THEN a.RK_ZL end) month3
9 from temp a
10 union all
11 select 'fw_zl' COLUMN_NAME, max(case when a.month='201008' THEN a.FW_ZL end) month1,
12 max(case when a.month='201009' THEN a.FW_ZL end) month2,
13 max(case when a.month='201009' THEN a.FW_ZL end) month3
14 from temp a
15 union all
16 select 'zzl' COLUMN_NAME, max(case when a.month='201008' THEN a.FW_ZL end) month1,
17 max(case when a.month='201009' THEN a.FW_ZL end) month2,
18 max(case when a.month='201009' THEN a.FW_ZL end) month3
19 from temp a
20 ;
COLUMN_NAME MONTH1 MONTH2 MONTH3
----------- ---------- ---------- ----------
指标项 201008 201009 201009
rk_zl 9500 1000 1000
fw_zl 100 200 200
zzl 100 200 200
SQL>
/*
标题:90度旋转行列转换之一
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2010-05-08
地点:重庆航天职业学院
说明:无
*/
/*
数据库中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 语句实现?
*/
/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/
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<>@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 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月
-------- ------ -------- -------- --------
奖金 300 310 320 330
工资 100 110 120 130
福利 200 210 220 230(所影响的行数为 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
goSELECT * FROM
(
SELECT 考核月份,月份,金额 FROM
(SELECT 月份, 工资, 福利, 奖金 FROM Test) p
UNPIVOT
(金额 FOR 考核月份 IN (工资, 福利, 奖金))AS unpvt
) T
PIVOT
(MAX(金额) FOR 月份 in ([1月],[2月],[3月],[4月]))AS ptdrop table test/*
项目 1月 2月 3月 4月
-------- ------ -------- -------- --------
奖金 300 310 320 330
工资 100 110 120 130
福利 200 210 220 230(3 行受影响)
*/
month stat_type value
201008 rk_zl 9500
201009 rk_zl 1000
201010 rk_zl 4000
201008 fw_zl 100
201009 fw_zl 200
201010 fw_zl 200
201008 zzl 0.23
201009 zzl 0.35
201010 zzl 0.41
SQL> select '指标项'COLUMN_NAME, max(TO_NUMBER(case when a.month='201008' THEN a.month end)) month1,
2 max(TO_NUMBER(case when a.month='201009' THEN a.month end)) month2,
3 max(TO_NUMBER(case when a.month='201009' THEN a.month end)) month3
4 from temp a
5 union all
6 select 'rk_zl' COLUMN_NAME, max(case when a.month='201008' THEN a.RK_ZL end) month1,
7 max(case when a.month='201009' THEN a.RK_ZL end) month2,
8 max(case when a.month='201009' THEN a.RK_ZL end) month3
9 from temp a
10 union all
11 select 'fw_zl' COLUMN_NAME, max(case when a.month='201008' THEN a.FW_ZL end) month1,
12 max(case when a.month='201009' THEN a.FW_ZL end) month2,
13 max(case when a.month='201009' THEN a.FW_ZL end) month3
14 from temp a
15 union all
16 select 'zzl' COLUMN_NAME, max(case when a.month='201008' THEN a.zzl end) month1,
17 max(case when a.month='201009' THEN a.zzl end) month2,
18 max(case when a.month='201009' THEN a.zzl end) month3
19 from temp a
20 ;
COLUMN_NAME MONTH1 MONTH2 MONTH3
----------- ---------- ---------- ----------
指标项 201008 201009 201009
rk_zl 9500 1000 1000
fw_zl 100 200 200
zzl 0.23 0.35 0.35
SQL>
1 with tb as
2 (
3 select '201008' month,9500 rk_zl,100 fw_zl,0.23 zzl from dual union all
4 select '201009',1000,200,0.35 from dual union all
5 select '201010',4000,200,0.41 from dual
6 )
7 select 'rk_zl' "标识",max(decode(month,'201008',rk_zl)) "201008",max(decode(month,'201009',rk_z
8 max(decode(month,'201010',rk_zl)) "201010" from tb
9 union all
10 select 'fw_zl' "标识",max(decode(month,'201008',fw_zl)) "201008",max(decode(month,'201009',fw_z
11 max(decode(month,'201010',fw_zl)) "201010" from tb
12 union all
13 select 'zzl' "标识",max(decode(month,'201008',zzl)) "201008",max(decode(month,'201009',zzl)) "2
14* max(decode(month,'201010',zzl)) "201010" from tb
SQL> /标识 201008 201009 201010
----- ---------- ---------- ----------
rk_zl 9500 1000 4000
fw_zl 100 200 200
zzl .23 .35 .41