/*
普通行列转换
假设有张学生成绩表(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