sql 行列互换 CASE WHEN
来源:互联网 发布:百度和知乎的区别 编辑:程序博客网 时间:2024/06/03 04:10
- http://chengyu2099.javaeye.com/blog/580898
- /*
- 普通行列转换(2007-11-18于海南三亚)
-
- 假设有张学生成绩表(tb)如下:
- Name Subject Result
- 张三 语文 74
- 张三 数学 83
- 张三 物理 93
- 李四 语文 74
- 李四 数学 84
- 李四 物理 94
- */
-
-
- /*
- 想变成
- 姓名 语文 数学 物理
-
- 李四 74 84 94
- 张三 74 83 93
- */
-
- create table tb
- (
- Name varchar(10) ,
- Subject varchar(10) ,
- Result int
- )
-
- insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
- insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
- insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
- insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
- insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
- insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
- go
-
-
- select name 姓名,
- max(case subject when '语文' then result else 0 end) 语文,
- max(case subject when '数学' then result else 0 end) 数学,
- max(case subject when '物理' then result else 0 end) 物理
- from tb
- group by name
- /*
- 姓名 语文 数学 物理
-
- 李四 74 84 94
- 张三 74 83 93
- */
-
-
- declare @sql varchar(8000)
- set @sql = 'select Name as ' + '姓名'
- select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
- from (select distinct Subject from tb) as a
- set @sql = @sql + ' from tb group by name'
- exec(@sql)
- /*
- 姓名 数学 物理 语文
-
- 李四 84 94 74
- 张三 83 93 74
- */
-
-
- /*加个平均分,总分
- 姓名 语文 数学 物理 平均分 总分
-
- 李四 74 84 94 84.00 252
- 张三 74 83 93 83.33 250
- */
-
-
- select name 姓名,
- max(case subject when '语文' then result else 0 end) 语文,
- max(case subject when '数学' then result else 0 end) 数学,
- max(case subject when '物理' then result else 0 end) 物理,
- cast(avg(result*1.0) as decimal(18,2)) 平均分,
- sum(result) 总分
- from tb
- group by name
- /*
- 姓名 语文 数学 物理 平均分 总分
-
- 李四 74 84 94 84.00 252
- 张三 74 83 93 83.33 250
- */
-
-
- declare @sql1 varchar(8000)
- set @sql1 = 'select Name as ' + '姓名'
- select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
- from (select distinct Subject from tb) as a
- set @sql1 = @sql1 + ' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
- exec(@sql1)
- /*
- 姓名 数学 物理 语文 平均分 总分
-
- 李四 84 94 74 84.00 252
- 张三 83 93 74 83.33 250
- */
-
- drop table tb
-
-
-
- /*
- 如果上述两表互相换一下:即
-
- 姓名 语文 数学 物理
- 张三 74 83 93
- 李四 74 84 94
-
- 想变成
- Name Subject Result
-
- 李四 语文 74
- 李四 数学 84
- 李四 物理 94
- 张三 语文 74
- 张三 数学 83
- 张三 物理 93
- */
-
- create table tb1
- (
- 姓名 varchar(10) ,
- 语文 int ,
- 数学 int ,
- 物理 int
- )
-
- insert into tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
- insert into tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)
-
- select * from
- (
- select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
- union all
- select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
- union all
- select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
- ) t
- order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end
-
-
- /*加个平均分,总分
- Name Subject Result
-
- 李四 语文 74.00
- 李四 数学 84.00
- 李四 物理 94.00
- 李四 平均分 84.00
- 李四 总分 252.00
- 张三 语文 74.00
- 张三 数学 83.00
- 张三 物理 93.00
- 张三 平均分 83.33
- 张三 总分 250.00
- */
-
- select * from
- (
- select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1
- union all
- select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
- union all
- select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
- union all
- select 姓名 as Name , Subject = '平均分' , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1
- union all
- select 姓名 as Name , Subject = '总分' , Result = 语文 + 数学 + 物理 from tb1
- ) t
- order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end
-
- drop table tb1