if not object_id('ta') is null drop table ta Go Create table ta([题号] int,[题型] nvarchar(3),[姓名] nvarchar(2),[部门] nvarchar(5),[年份] Datetime) Insert ta select 1,N'选择题',N'人四',N'人力资源部','2010' union all select 2,N'填空题',N'人四',N'人力资源部','2010' union all select 3,N'问答题',N'人四',N'人力资源部','2010' union all select 4,N'问答题',N'人四',N'人力资源部','2010' union all select 5,N'选择题',N'销三',N'销售部','2010' union all select 6,N'填空题',N'销三',N'销售部','2010' union all select 7,N'判断题',N'销三',N'销售部','2010' union all select 8,N'选择题',N'销四',N'销售部','2009' union all select 9,N'填空题',N'销四',N'销售部','2009' union all select 10,N'叙述题',N'销四',N'销售部','2009' Go if not object_id('tb') is null drop table tb Go Create table tb([题号] int,[成绩] int) Insert tb select 1,2 union all select 2,3 union all select 3,1 union all select 4,10 union all select 5,10 union all select 6,5 union all select 7,10 union all select 8,5 union all select 9,12 union all select 10,5 Go declare @sql nvarchar(4000) set @sql='' select @sql = @sql+ N', sum(case [题型] when N''' + [题型] + N''' then [成绩] else 0 end) [' + [题型] + ']' from (select distinct [题型]from ta) as a set @sql = @sql + N',sum([成绩])总分,姓名,部门,年份 from ta a,tb b where a.[题号]=b.[题号]' set @sql=@sql+N'group by 姓名,部门,年份' set @sql='select '+right(@sql,len(@sql)-1) exec(@sql) /* 判断题 填空题 叙述题 选择题 问答题 总分 姓名 部门 年份 ----------- ----------- ----------- ----------- ----------- ----------- ---- ----- ----------------------- 0 3 0 2 11 16 人四 人力资源部 2010-01-01 00:00:00.000 10 5 0 10 0 25 销三 销售部 2010-01-01 00:00:00.000 0 12 5 5 0 22 销四 销售部 2009-01-01 00:00:00.000*/
if object_id('[题库]') is not null drop table [题库] go create table [题库]([题号] int,[题型] varchar(6),[姓名] varchar(4),[部门] varchar(10),[年份] int) insert [题库] select 1,'选择题','人四','人力资源部',2010 union all select 2,'填空题','人四','人力资源部',2010 union all select 3,'问答题','人四','人力资源部',2010 union all select 4,'问答题','人四','人力资源部',2010 union all select 5,'选择题','销三','销售部',2010 union all select 6,'填空题','销三','销售部',2010 union all select 7,'判断题','销三','销售部',2010 union all select 8,'选择题','销四','销售部',2009 union all select 9,'填空题','销四','销售部',2009 union all select 10,'叙述题','销四','销售部',2009 if object_id('[成绩]') is not null drop table [成绩] go create table [成绩]([题号] int,[成绩] int) insert [成绩] select 1,2 union all select 2,3 union all select 3,1 union all select 4,10 union all select 5,10 union all select 6,5 union all select 7,10 union all select 8,5 union all select 9,12 union all select 10,5declare @dept varchar(20),@sql varchar(max) set @dept='人力资源部' select @sql=isnull(@sql+',','')+'sum(case when a.题型='''+题型+''' then 1 else 0 end) as ['+题型+']' from(select distinct 题型 from 题库 where 部门=@dept)t exec ('select ' +@sql +',sum(b.成绩)总分,a.姓名,a.部门,a.年份 from 题库 a,成绩 b where a.题号=b.题号 and a.部门=''' +@dept +''' group by a.姓名,a.部门,a.年份' )/** 填空题 问答题 选择题 总分 姓名 部门 年份 ----------- ----------- ----------- ----------- ---- ---------- ----------- 1 2 1 16 人四 人力资源部 2010(1 行受影响)**/
嘻嘻,谢谢,楼主,用动态SQL啊,他们都这么说的
drop table ta
Go
Create table ta([题号] int,[题型] nvarchar(3),[姓名] nvarchar(2),[部门] nvarchar(5),[年份] Datetime)
Insert ta
select 1,N'选择题',N'人四',N'人力资源部','2010' union all
select 2,N'填空题',N'人四',N'人力资源部','2010' union all
select 3,N'问答题',N'人四',N'人力资源部','2010' union all
select 4,N'问答题',N'人四',N'人力资源部','2010' union all
select 5,N'选择题',N'销三',N'销售部','2010' union all
select 6,N'填空题',N'销三',N'销售部','2010' union all
select 7,N'判断题',N'销三',N'销售部','2010' union all
select 8,N'选择题',N'销四',N'销售部','2009' union all
select 9,N'填空题',N'销四',N'销售部','2009' union all
select 10,N'叙述题',N'销四',N'销售部','2009'
Go
if not object_id('tb') is null
drop table tb
Go
Create table tb([题号] int,[成绩] int)
Insert tb
select 1,2 union all
select 2,3 union all
select 3,1 union all
select 4,10 union all
select 5,10 union all
select 6,5 union all
select 7,10 union all
select 8,5 union all
select 9,12 union all
select 10,5
Go
declare @sql nvarchar(4000)
set @sql=''
select @sql = @sql+ N', sum(case [题型] when N''' + [题型] + N''' then [成绩] else 0 end) [' + [题型] + ']'
from (select distinct [题型]from ta) as a
set @sql = @sql + N',sum([成绩])总分,姓名,部门,年份 from ta a,tb b where a.[题号]=b.[题号]'
set @sql=@sql+N'group by 姓名,部门,年份'
set @sql='select '+right(@sql,len(@sql)-1)
exec(@sql)
/*
判断题 填空题 叙述题 选择题 问答题 总分 姓名 部门 年份
----------- ----------- ----------- ----------- ----------- ----------- ---- ----- -----------------------
0 3 0 2 11 16 人四 人力资源部 2010-01-01 00:00:00.000
10 5 0 10 0 25 销三 销售部 2010-01-01 00:00:00.000
0 12 5 5 0 22 销四 销售部 2009-01-01 00:00:00.000*/
go
create table [题库]([题号] int,[题型] varchar(6),[姓名] varchar(4),[部门] varchar(10),[年份] int)
insert [题库]
select 1,'选择题','人四','人力资源部',2010 union all
select 2,'填空题','人四','人力资源部',2010 union all
select 3,'问答题','人四','人力资源部',2010 union all
select 4,'问答题','人四','人力资源部',2010 union all
select 5,'选择题','销三','销售部',2010 union all
select 6,'填空题','销三','销售部',2010 union all
select 7,'判断题','销三','销售部',2010 union all
select 8,'选择题','销四','销售部',2009 union all
select 9,'填空题','销四','销售部',2009 union all
select 10,'叙述题','销四','销售部',2009
if object_id('[成绩]') is not null drop table [成绩]
go
create table [成绩]([题号] int,[成绩] int)
insert [成绩]
select 1,2 union all
select 2,3 union all
select 3,1 union all
select 4,10 union all
select 5,10 union all
select 6,5 union all
select 7,10 union all
select 8,5 union all
select 9,12 union all
select 10,5declare @dept varchar(20),@sql varchar(max)
set @dept='人力资源部'
select @sql=isnull(@sql+',','')+'sum(case when a.题型='''+题型+''' then 1 else 0 end) as ['+题型+']'
from(select distinct 题型 from 题库 where 部门=@dept)t
exec ('select '
+@sql
+',sum(b.成绩)总分,a.姓名,a.部门,a.年份 from 题库 a,成绩 b where a.题号=b.题号 and a.部门='''
+@dept
+''' group by a.姓名,a.部门,a.年份'
)/**
填空题 问答题 选择题 总分 姓名 部门 年份
----------- ----------- ----------- ----------- ---- ---------- -----------
1 2 1 16 人四 人力资源部 2010(1 行受影响)**/