行转列..SQL

create table #score(personID int , date datetime , score int )
insert into #score
select 1     ,  '2007-12-2 06:00:12',      10
union select 2     ,  '2007-12-1 00:12:12'  ,    20
union select 1     , '2007-12-1 12:12:20'  ,     30
union select 1     , '2007-12-1 12:05:05'   ,    40
union select 3     , '2007-12-5 04:04:05'   ,    50

create table #person (PersonID int, pName varchar(30))
insert into #person
select 1    ,'a'
union select 2    ,'b'
union select 3    ,'c'
union select 4    ,'d'
union select 5    ,'e'
/*
要求出每个人，2007-12-01到2007-12-5每一天的成绩，
如果这个这天有两个以上的成绩就求平均，
最后还要对这个五天的成绩再做平均
SQL执行后的结果集如下，请问SQL怎么写？
人员编号  姓名  2007-12-01 2007-12-02 2007-12-03 2007-12-04 2007-12-05
   1       a   35  10      -            -           -
  (注：2007-12-1这个人有两个成绩，要求平均,这天没有成绩的空着或者画一个‘-’)
   2       b      20
   3       c                                                     50
   4       d
   5       e
*/
drop table #avgS
create table #avgS (personID int , date varchar(10) , score int )
insert into #avgS
select personID ,convert (varchar(30) ,date,110)  ,avg(score) as s
from #score
group by personID ,convert (varchar(30) ,date,110)

select * from  #avgS

//用临时表:
select v.personID, 
sum(case when cast (v.date as varchar(10) ) = '12-01-2007'
then v.score else '0' end ) as "2007-12-1",
sum(case when cast (v.date as varchar(10) ) = '12-02-2007'
then v.score else '0' end ) as "2007-12-2"  ,
sum(case when cast (v.date as varchar(10) ) = '12-03-2007'
then v.score else '0' end ) as "2007-12-3",
sum(case when cast (v.date as varchar(10) ) = '12-04-2007'
then v.score else '0' end ) as "2007-12-4",
sum(case when cast (v.date as varchar(10) ) = '12-05-2007'
then v.score else '0' end ) as "2007-12-5" ,
avg(v.score) as avg_score
from  #avgS as v
group by v.personID

//结果:
personID    2007-12-1   2007-12-2   2007-12-3   2007-12-4   2007-12-5   avg_score
----------- ----------- ----------- ----------- ----------- ----------- -----------
1           35          10          0           0           0           22
2           20          0           0           0           0           20
3           0           0           0           0           50          50

(3 行受影响)


//而不用临时表:
select v.personID,  
sum(case when cast (v.date as varchar(10) ) = '12-01-2007'
then v.score else '0' end ) as "2007-12-1",
sum(case when cast (v.date as varchar(10) ) = '12-02-2007'
then v.score else '0' end ) as "2007-12-2"  ,
sum(case when cast (v.date as varchar(10) ) = '12-03-2007'
then v.score else '0' end ) as "2007-12-3",
sum(case when cast (v.date as varchar(10) ) = '12-04-2007'
then v.score else '0' end ) as "2007-12-4",
sum(case when cast (v.date as varchar(10) ) = '12-05-2007'
then v.score else '0' end ) as "2007-12-5" ,
avg(v.score) as avg_score
from (
select personID ,cast (date as varchar(10) ) as date ,avg(score) as score
from #score
group by personID ,cast (date as varchar(10) )
) as v
group by v.personID


结果
personID    2007-12-1   2007-12-2   2007-12-3   2007-12-4   2007-12-5   avg_score
----------- ----------- ----------- ----------- ----------- ----------- -----------
1           0           0           0           0           0           22
2           0           0           0           0           0           20
3           0           0           0           0           0           50

(3 行受影响)


现在出现的问题是: 用和不用临时表,结果是不一样的.