/* 标题:普通行列转换(version 2.0) 作者:爱新觉罗.毓华 时间:2008-03-09 地点:广东深圳 说明:普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法,version 2.0增加sql server 2005的有关写法。 问题:假设有张学生成绩表(tb)如下: 姓名 课程 分数 张三 语文 74 张三 数学 83 张三 物理 93 李四 语文 74 李四 数学 84 李四 物理 94 想变成(得到如下结果): 姓名 语文 数学 物理 ---- ---- ---- ---- 李四 74 84 94 张三 74 83 93 ------------------- */ create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int) insert into tb values('张三' , '语文' , 74) insert into tb values('张三' , '数学' , 83) insert into tb values('张三' , '物理' , 93) insert into tb values('李四' , '语文' , 74) insert into tb values('李四' , '数学' , 84) insert into tb values('李四' , '物理' , 94) go --SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同) select 姓名 as 姓名 , max(case 课程 when '语文' then 分数 else 0 end) 语文, max(case 课程 when '数学' then 分数 else 0 end) 数学, max(case 课程 when '物理' then 分数 else 0 end) 物理 from tb group by 姓名 --SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同) declare @sql varchar(8000) set @sql = 'select 姓名 ' select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']' from (select distinct 课程 from tb) as a set @sql = @sql + ' from tb group by 姓名' exec(@sql) --SQL SERVER 2005 静态SQL。 select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b --SQL SERVER 2005 动态SQL。 declare @sql varchar(8000) select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程 set @sql = '[' + @sql + ']' exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b') --------------------------------- /* 问题:在上述结果的基础上加平均分,总分,得到如下结果: 姓名 语文 数学 物理 平均分 总分 ---- ---- ---- ---- ------ ---- 李四 74 84 94 84.00 252 张三 74 83 93 83.33 250 */ --SQL SERVER 2000 静态SQL。 select 姓名 姓名, max(case 课程 when '语文' then 分数 else 0 end) 语文, max(case 课程 when '数学' then 分数 else 0 end) 数学, max(case 课程 when '物理' then 分数 else 0 end) 物理, cast(avg(分数*1.0) as decimal(18,2)) 平均分, sum(分数) 总分 from tb group by 姓名 --SQL SERVER 2000 动态SQL。 declare @sql varchar(8000) set @sql = 'select 姓名 ' select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']' from (select distinct 课程 from tb) as a set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名' exec(@sql) --SQL SERVER 2005 静态SQL。 select m.* , n.平均分 , n.总分 from (select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m, (select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n where m.姓名 = n.姓名 --SQL SERVER 2005 动态SQL。 declare @sql varchar(8000) select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程 exec ('select m.* , n.平均分 , n.总分 from (select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , (select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n where m.姓名 = n.姓名') drop table tb ------------------ ------------------ /* 问题:如果上述两表互相换一下:即表结构和数据为: 姓名 语文 数学 物理 张三 74 83 93 李四 74 84 94 想变成(得到如下结果): 姓名 课程 分数 ---- ---- ---- 李四 语文 74 李四 数学 84 李四 物理 94 张三 语文 74 张三 数学 83 张三 物理 93 -------------- */ create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int) insert into tb values('张三',74,83,93) insert into tb values('李四',74,84,94) go --SQL SERVER 2000 静态SQL。 select * from ( select 姓名 , 课程 = '语文' , 分数 = 语文 from tb union all select 姓名 , 课程 = '数学' , 分数 = 数学 from tb union all select 姓名 , 课程 = '物理' , 分数 = 物理 from tb ) t order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end --SQL SERVER 2000 动态SQL。 --调用系统表动态生态。 declare @sql varchar(8000) select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb' from syscolumns where name! = N'姓名' and ID = object_id('tb') --表名tb,不包含列名为姓名的其它列 order by colid asc exec(@sql + ' order by 姓名 ') --SQL SERVER 2005 动态SQL。 select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t --SQL SERVER 2005 动态SQL,同SQL SERVER 2000 动态SQL。 -------------------- /* 问题:在上述的结果上加个平均分,总分,得到如下结果: 姓名 课程 分数 ---- ------ ------ 李四 语文 74.00 李四 数学 84.00 李四 物理 94.00 李四 平均分 84.00 李四 总分 252.00 张三 语文 74.00 张三 数学 83.00 张三 物理 93.00 张三 平均分 83.33 张三 总分 250.00 ------------------ */ select * from ( select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb union all select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb union all select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb union all select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb union all select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb ) t order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end drop table tb -------------------------------------------------- 以下是 dawugui 回贴 -------------------------------------------------- create table A(id char(3), num1 int, num2 int, num3 int, num4 int) insert A select '001', 80, 90, 50, 60 insert A select '002', 84, 70, 60, 82 go --SQL2005实现方法: select * from A unpivot (num for col in([num1],[num2],[num3],[num4]))T2)tmp --SQL2000实现: ---调用系统表动态生态 declare @s nvarchar(4000) select @s=isnull(@s+' union all ','')+' select ID,[num]='+quotename(Name,'''')+',Qty='+quotename(Name)+' from A' from syscolumns where Name!=N'ID' and ID=object_id('A')--表名A,不包含列名为ID的其它列 order by colid asc exec(@s+' order by ID asc,[num] asc') --生成的静态语句 select ID,[num]='num1',Qty=[num1] from A union all select ID,[num]='num2',Qty=[num2] from A union all select ID,[num]='num3',Qty=[num3] from A union all select ID,[num]='num4',Qty=[num4] from A order by ID asc,[num] asc /* ID num Qty ---- ---- ----------- 001 num1 80 001 num2 90 001 num3 50 001 num4 60 002 num1 84 002 num2 70 002 num3 60 002 num4 82 ------------------------------ */ --动态方法: declare @s nvarchar(4000) select @s=isnull(@s+' union all ','')+' select ID,[num]='+quotename(Name)+' from A' from syscolumns where Name!=N'ID' and ID=object_id('A') order by colid asc exec(@s+' order by ID asc') --生成的语句如下: select ID,[num]=[num1] from A union all select ID,[num]=[num2] from A union all select ID,[num]=[num3] from A union all select ID,[num]=[num4] from A order by ID asc,[num] asc /* ID num ---- ----------- 001 80 001 90 001 50 001 60 002 82 002 60 002 70 002 84 */ ---drop table A /* 将表数据旋转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
sql所有行列转换
最新推荐文章于 2023-05-10 17:19:59 发布