- 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
- --静态SQL,指subject只有语文、数学、物理这三门课程。
- 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
- */
- --动态SQL,指subject不止语文、数学、物理这三门课程。
- 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
- */
- --静态SQL,指subject只有语文、数学、物理这三门课程。
- 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
- */
- --动态SQL,指subject不止语文、数学、物理这三门课程。
- 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
sql 行列互换 CASE WHEN
最新推荐文章于 2023-05-10 17:19:59 发布