刚才发帖说的不清楚, 抱歉啊 感谢大家给予回复现在重新描述:表一结构是:姓名 科目 分数
张三 数学 33
张三 语文 44
李四 数学 33
李四 语文 44
李四 外语 44表二结构是:
姓名 数学 语文 外语
张三 33 44 0
李四 33 44 44
对于表一中张三没有的科目在表二中用0代替把表一数据复制到表二 求完整的sql语句
张三 数学 33
张三 语文 44
李四 数学 33
李四 语文 44
李四 外语 44表二结构是:
姓名 数学 语文 外语
张三 33 44 0
李四 33 44 44
对于表一中张三没有的科目在表二中用0代替把表一数据复制到表二 求完整的sql语句
/*
标题:普通行列转换(version 2.0)
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:-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
--你要全部插入么?insert into t1
select *
from(
select 姓名,'数学' as 科目,数学 as 分数 from t2
union all
select 姓名,'语文' as 科目,语文 as 分数 from t2
union all
select 姓名,'英语' as 科目,英语 as 分数 from t2
)t
--你要全部插入么?insert into t1
select *
from(
select 姓名,'数学' as 科目,数学 as 分数 from t2
union all
select 姓名,'语文' as 科目,语文 as 分数 from t2
union all
select 姓名,'英语' as 科目,英语 as 分数 from t2
)t
select 姓名as 姓名,
max(case 课程when '语文' then 分数else 0 end) 语文,
max(case 课程when '数学' then 分数else 0 end) 数学,
max(case 课程when '物理' then 分数else 0 end) 物理
inbo 新表表名
from tb
group by 姓名