这个语句怎么写

[windstar]

请问这个语句怎么写？
有两个表
一个员工表
一个考核成绩表
格式如下：

CREATE TABLE [employee] (
[employee_id] [varchar] (12) ,
[name] [varchar] (10) ,
[dept_code] [varchar] (12) ,
[starting_date] [datetime] ,
[leaved_flag] [varchar] (1) ,//Y or N
)


CREATE TABLE [evaluate_socre] (
[employee_id] [varchar] (12) ,
[period_id] [int],
[score] [numeric](10, 0)
)

其中内容大致如下：
100001
张三
01
2003-01-01
Y

100001
01
78
.
.
.
1000001
12
69

我想取得如下格式的信息（在职员工全年每月度的考核分及平均分）
工号 姓名 部门 入职日期 一月 …… 十二月 全年平均分
100001 张三 人事处 2003-01-01 78 …… 69 76

请问这个语句如何写?


我知道可以类似
declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)

的语句来得到结果，但是我看不懂这条语句的具体含义，麻烦哪位帮忙解答and解释一下？谢谢

[txlicenhe]

其实就是把
case 月份 when '1月' then 成绩 as [1月成绩]
when '2月' then 成绩 as [2月成绩]
when '3月' then 成绩 as [3月成绩]
when '4月' then 成绩 as [4月成绩]
when '5月' then 成绩 as [5月成绩]
...
换成动态的语句。

[zjcxc]

declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
print(@sql) --这样你就看到具体执行的是什么了.
exec(@sql)

[xiaoliaoyun]

declare @sql varchar(8000) --建立一个名字为sql的变量
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name' --其实以上的语句可以写在一起的， -- 大概是怕一行显示的太多，所以分开了吧。实际就是一条select 语句,'+'是连接符
exec(@sql) --执行该语句

[flynn1981]

麻烦txlicenhe(马可)把具体代码写出来,下面是create表

CREATE TABLE employee(
employee_id varchar(12),
name varchar(10) ,
dept_code varchar(12) ,
starting_date datetime ,
leaved_flag varchar(1)--Y or N
)

CREATE TABLE evaluate_score(
employee_id varchar(12),
period_id int,
score numeric(10,0)
)
insert into employee
values('100001','张三','2003-1-1','Y')

insert into evaluate_score
values('100001','01','78')
insert into evaluate_score
values('100001','02','70')
insert into evaluate_score
values('100001','03','79')
insert into evaluate_score
values('100001','04','69')
insert into evaluate_score
values('100001','05','78')
insert into evaluate_score
values('100001','06','70')
insert into evaluate_score
values('100001','07','72')
insert into evaluate_score
values('100001','08','67')
insert into evaluate_score
values('100001','09','70')
insert into evaluate_score
values('100001','10','76')
insert into evaluate_score
values('100001','11','71')
insert into evaluate_score
values('100001','12','78')

[flynn1981]

一时大意，不好意思，请复制下面的
CREATE TABLE employee(
employee_id varchar(12),
name varchar(10) ,
dept_code varchar(12) ,
starting_date datetime ,
leaved_flag varchar(1)--Y or N
)

CREATE TABLE evaluate_score(
employee_id varchar(12),
period_id int,
score numeric(10,0)
)


insert into employee
values('100001','张三','2003-1-1','Y')

insert into evaluate_score
values('100001',01,'78')
insert into evaluate_score
values('100001',02,'70')
insert into evaluate_score
values('100001',03,'79')
insert into evaluate_score
values('100001',04,'69')
insert into evaluate_score
values('100001',05,'78')
insert into evaluate_score
values('100001',06,'70')
insert into evaluate_score
values('100001',07,'72')
insert into evaluate_score
values('100001',08,'67')
insert into evaluate_score
values('100001',09,'70')
insert into evaluate_score
values('100001',10,'76')
insert into evaluate_score
values('100001',11,'71')
insert into evaluate_score
values('100001',12,'78')

[windstar]

我现在用
select employee_id,
sum(case period_id when '09' then score end) [一月],
sum(case period_id when '10' then score end) [二月],
sum(case period_id when '11' then score end) [三月],
sum(case period_id when '12' then score end) [四月],
sum(case period_id when '13' then score end) [五月],
sum(case period_id when '14' then score end) [六月],
sum(case period_id when '15' then score end) [七月],
sum(case period_id when '16' then score end) [八月],
sum(case period_id when '17' then score end) [九月],
sum(case period_id when '18' then score end) [十月],
sum(case period_id when '19' then score end) [十一月],
sum(case period_id when '20' then score end) [十二月],
AVG(score) [平均分]
into score_2003
from evaluate_remark
group by employee_id
将以上数据插到score_2003表中
然后用
select employee.employee_id,name,dept_class.dept_name,position.posi_name,employee.starting_date,
score_2003.一月,score_2003.二月,score_2003.三月,score_2003.四月,score_2003.五月,score_2003.六月,score_2003.七月,
score_2003.八月,score_2003.九月,score_2003.三月,score_2003.十月,score_2003.三月,score_2003.十一月,score_2003.十二月,score_2003.平均分
from employee
left join score_2003 on(employee.employee_id = score_2003.employee_id)
left join dept_class on (employee.dept_code =dept_class.dept_code)
left join position on (employee.posi_code = position.posi_code)
把所需要的数据查出来了

上面那条语句中
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'

的'''+km+''' 为什么要这么用，这么多'，为什么？