if not object_id('Class') is null drop table Class Go Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int) Insert Class select N'张三',N'语文',78 union all select N'张三',N'数学',87 union all select N'张三',N'英语',82 union all select N'张三',N'物理',90 union all select N'李四',N'语文',65 union all select N'李四',N'数学',77 union all select N'李四',N'英语',65 union all select N'李四',N'物理',85 Go --2000方法: 动态:declare @s nvarchar(4000) set @s='' Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)' from Class group by[Course] exec('select [Student]'+@s+' from Class group by [Student]') 生成静态:select [Student], [数学]=max(case when [Course]='数学' then [Score] else 0 end), [物理]=max(case when [Course]='物理' then [Score] else 0 end), [英语]=max(case when [Course]='英语' then [Score] else 0 end), [语文]=max(case when [Course]='语文' then [Score] else 0 end) from Class group by [Student]GO 动态:declare @s nvarchar(4000) Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')生成静态: select * from Class pivot (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b生成格式: /* Student 数学 物理 英语 语文 ------- ----------- ----------- ----------- ----------- 李四 77 85 65 65 张三 87 90 82 78(2 行受影响) */------------------------------------------------------------------------------------------ go --加上总成绩(学科平均分)--2000方法: 动态:declare @s nvarchar(4000) set @s='' Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)' from Class group by[Course] exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select [Student], [数学]=max(case when [Course]='数学' then [Score] else 0 end), [物理]=max(case when [Course]='物理' then [Score] else 0 end), [英语]=max(case when [Course]='英语' then [Score] else 0 end), [语文]=max(case when [Course]='语文' then [Score] else 0 end), [总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score])) from Class group by [Student]go--2005方法:动态:declare @s nvarchar(4000) Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号 exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a pivot (max([Score]) for [Course] in('+@s+'))b ')生成静态:select [Student],[数学],[物理],[英语],[语文],[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score]) pivot (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/* Student 数学 物理 英语 语文 总成绩 ------- ----------- ----------- ----------- ----------- ----------- 李四 77 85 65 65 292 张三 87 90 82 78 337(2 行受影响) */go--2、列转行 --> --> (Roy)生成測試數據
if not object_id('Class') is null drop table Class Go Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int) Insert Class select N'李四',77,85,65,65 union all select N'张三',87,90,82,78 Go--2000:动态:declare @s nvarchar(4000) select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all +',[Score]='+quotename(Name)+' from Class' from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列 order by Colid exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态: select * from (select [Student],[Course]='数学',[Score]=[数学] from Class union all select [Student],[Course]='物理',[Score]=[物理] from Class union all select [Student],[Course]='英语',[Score]=[英语] from Class union all select [Student],[Course]='语文',[Score]=[语文] from Class)t order by [Student],[Course]go --2005:动态:declare @s nvarchar(4000) select @s=isnull(@s+',','')+quotename(Name) from syscolumns where ID=object_id('Class') and Name not in('Student') order by Colid exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式: /* Student Course Score ------- ------- ----------- 李四 数学 77 李四 物理 85 李四 英语 65 李四 语文 65 张三 数学 87 张三 物理 90 张三 英语 82 张三 语文 78(8 行受影响) */
当不知道多少列时,怎么办呢,只用SQL语句,做报表(PB)?
/* 将表数据旋转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 colidopen t_cursorfetch next from t_cursor into @namewhile @@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) 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 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 项目种类 descdrop 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<>@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 goexec 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
--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子,比较形象易懂整理人:中国风(Roy)日期:2008.06.06
******************************************************************************************************************************************************/--1、行互列
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
生成静态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]GO
动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78(2 行受影响)
*/------------------------------------------------------------------------------------------
go
--加上总成绩(学科平均分)--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end),
[总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from
Class
group by [Student]go--2005方法:动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')生成静态:select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337(2 行受影响)
*/go--2、列转行
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go--2000:动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]go
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78(8 行受影响)
*/
将表数据旋转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 colidopen t_cursorfetch next from t_cursor into @namewhile @@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)
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 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 项目种类 descdrop 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<>@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
goexec 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
insert into tb values('a' ,1, 2, 3 )
insert into tb values('b' ,4, 5, 6 )
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<>@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--用上面的存储过程测试:
exec p_zj 'tb', 'col1' , 'col1'drop table tbdrop proc p_zj/*
col1 a b
---- ----------- -----------
col2 1 4
col3 2 5
col4 3 6(所影响的行数为 3 行)
*/