求行转列问题,看了很多但还是不太明白

[cjlxm1]

数据结构:
subject 课程表
id,pid1,pid2,subname,flag
1 2 3 语文 1
2 2 4 数学 1
3 5 4 英语 0
4 3 3 物理 1
...

student 考试人员表
empno name dept
001 张三 人事部
002 李四 会计部
008 王五 物流部
...

score 考试成绩表
pid pid1,pid2,empno,score
1 3 3 008 80
2 2 4 008 80
3 2 4 001 100
4 2 3 002 95

查询结果:
要求显示student表中所有人员的所有flag=1的科目(subject表的flag=1)的成绩,并且subject表的行数是不固定的.
详细如下:
empno name dept 语文 数学 物理 ...
008 王五 物流部 0 80 80 ...
001 张三 人事部 0 100 0 ...
002 李四 会计部 95 0 0 ...

各位,欢迎解答.

[cjlxm1]

补充:
科目表subject的pid1,pid2确定subname
为了理解简单,我用人员参加考试的应用替换实际的应用,需根据pid1,pid2得到subname,但表的结构是相同的.

[bqb]

declare @subject table(id int,pid1 int,pid2 int,subname varchar(20),flag int)
insert into @subject
select 1, 2, 3, '语文' , 1
union all select 2, 2, 4, '数学', 1
union all select 3, 5, 4, '英语', 0
union all select 4, 3, 3, '物理', 1
--select * from @subject

declare @student table(empno varchar(10),name varchar(20), dept varchar(20))
insert into @student
select '001', '张三', '人事部'
union all select '002', '李四', '会计部'
union all select '008', '王五', '物流部'
--select * from @student

declare @score table (pid int, pid1 int,pid2 int,empno varchar(10),score int)
insert into @score
select 1, 3, 3, '008', 80
union all select 2, 2, 4, '008', 80
union all select 3, 2, 4, '001', 100
union all select 4, 2, 3, '002', 95
--select * from @score


if object_id('temp')<>'' drop table temp

select a.empno,c.name,c.dept,b.subname,a.score into temp from @score a
left join @subject b on a.pid1=b.pid1 and a.pid2=b.pid2 and b.flag=1
left join @student c on a.empno=c.empno



declare @e varchar(8000)
set @e='select empno,name,dept'
select @e=@e+',max(case when subname ='''+subname+''' then score else 0 end) as '''+subname+'''' from temp group by subname
select @e=@e+'from tt group by empno,name,dept'
exec (@e)


/*
empno name dept 数学 物理 语文
-------------------------------------------------------------------------------
001 张三 人事部 100 0 0
002 李四 会计部 0 0 95
008 王五 物流部 80 80 0
*/

[bqb]

多谢,但出错:

服务器: 消息 208，级别 16，状态 1，行 1
对象名 'tt' 无效。

不懂,请教!
******************************************************


declare @e varchar(8000)
set @e='select empno,name,dept'
select @e=@e+',max(case when subname ='''+subname+''' then score else 0 end) as '''+subname+'''' from temp group by subname
select @e=@e+'from temp group by empno,name,dept' -- 不好意思 temp写成tt 了
exec (@e)


其他的应该没有什么问题吧

[cjlxm1]

每次查询都要这样做吗?
不是要求查询一次就够了,而是有一个查询页面,管理员进去可查看所有人员的信息,和上面显示的一样,如非管理员进去的时候只能查看个人的信息,我想请问一下,
declare @subject table(id int,pid1 int,pid2 int,subname varchar(20),flag int)
insert into @subject
select 1, 2, 3, '语文' , 1
union all select 2, 2, 4, '数学', 1
union all select 3, 5, 4, '英语', 0
union all select 4, 3, 3, '物理', 1
--select * from @subject

declare @student table(empno varchar(10),name varchar(20), dept varchar(20))
insert into @student...

declare @score table (pid int, pid1 int,pid2 int,empno varchar(10),score int)
insert into @score...
每次都要这样写吗?在cs文件里又是怎样写叱?多谢.

[cjlxm1]

多谢,但出错:

服务器: 消息 208，级别 16，状态 1，行 1
对象名 'tt' 无效。

不懂,请教!

[ojuju10]

create table #subject (id int,pid1 int,pid2 int,subname varchar(20),flag int)
insert into #subject
select 1, 2, 3, '语文' , 1
union all select 2, 2, 4, '数学', 1
union all select 3, 5, 4, '英语', 0
union all select 4, 3, 3, '物理', 1
--select * from @subject

create table #student (empno varchar(10),name varchar(20), dept varchar(20))
insert into #student
select '001', '张三', '人事部'
union all select '002', '李四', '会计部'
union all select '008', '王五', '物流部'
--select * from @student

create table #score (pid int, pid1 int,pid2 int,empno varchar(10),score int)
insert into #score
select 1, 3, 3, '008', 80
union all select 2, 2, 4, '008', 80
union all select 3, 2, 4, '001', 100
union all select 4, 2, 3, '002', 95

select b.empno,b.name,dept,subname,score into # from #subject a,#student b,#score c
where b.empno=c.empno and a.pid1=c.pid1 and a.pid2=c.pid2 and a.flag=1

select * from #

---动态的
declare @sql varchar(1000)
set @sql='select empno,name,dept'
select @sql=@sql+' , isnull(max(case when subname='''+subname+''' then score else null end ),0)' + subname
from (select subname from # group by subname ) a
set @sql=@sql+' from # group by empno,name,dept'
exec(@sql)

----静态的
select empno,name,dept ,
isnull(max(case when subname='数学' then score else null end ),0) 数学 ,
isnull(max(case when subname='物理' then score else null end ),0) 物理 ,
isnull(max(case when subname='语文' then score else null end ),0) 语文
from # group by empno,name,dept


empno name dept 数学 物理 语文
---------- -------------------- -------------------- ----------- ----------- -----------
001 张三 人事部 100 0 0
002 李四 会计部 0 0 95
008 王五 物流部 80 80 0
警告: 聚合或其他 SET 操作消除了空值。

(3 行受影响)

[ojuju10]

还是用动态的sql吧，因为你的科目比较多

最好写个视图，这种写动态sql就比较的方便

[cjlxm1]

谢谢,科目比较多,而且是动态的,主要根据flag=1来确定
另写视图,我还不是明白,各位能能直接告诉我这个应怎样写.

[cjlxm1]

to ojuju10(longdchuanren:
由于这个查询是在页面执行的,所以需执行多次,
第一次能够正确执行,但第二次执行时会报错:

服务器: 消息 2714，级别 16，状态 6，行 1
数据库中已存在名为 '#subject' 的对象。

我写了这个:if object_id('#subject')<>'' drop table #subject
但还是不行,

[cjlxm1]

我写了这个:if object_id('#subject')<>'' drop table #subject
知道了,用这个 drop table #subject

[ye0804]

顶!

[dawugui]

create table subject(id int,pid1 int,pid2 int,subname varchar(10),flag int)
insert into subject values(1, 2, 3, '语文', 1)
insert into subject values(2, 2, 4, '数学', 1)
insert into subject values(3, 5, 4, '英语', 0)
insert into subject values(4, 3, 3, '物理', 1)
create table student(empno varchar(10), name varchar(10), dept varchar(10))
insert into student values('001', '张三', '人事部')
insert into student values('002', '李四', '会计部')
insert into student values('008', '王五', '物流部')
create table score(pid int,pid1 int,pid2 int,empno varchar(10),score int)
insert into score values(1, 3, 3, '008', 80)
insert into score values(2, 2, 4, '008', 80)
insert into score values(3, 2, 4, '001', 100)
insert into score values(4, 2, 3, '002', 95)
go
--静态SQL（本例不合适，但写在这里供参考）
select empno , name , dept ,
max(case subname when '语文' then score else 0 end) '语文',
max(case subname when '数学' then score else 0 end) '数学',
max(case subname when '物理' then score else 0 end) '物理'
from
(
select b.*,a.subname,c.score from subject a,student b,score c where b.empno = c.empno and a.pid1 = c.pid1 and a.pid2 = c.pid2 and a.flag = 1
) t
group by empno , name , dept
/*
empno name dept 语文 数学 物理
---------- ---------- ---------- ----------- ----------- ----
001 张三 人事部 0 100 0
002 李四 会计部 95 0 0
008 王五 物流部 0 80 80
（所影响的行数为 3 行）
*/
--动态SQL
declare @sql varchar(8000)
set @sql = 'select empno , name , dept'
select @sql = @sql + ' , max(case subname when ''' + subname + ''' then score else 0 end) [' + subname + ']'
from (select distinct subname from (select b.*,a.subname,c.score from subject a,student b,score c where b.empno = c.empno and a.pid1 = c.pid1 and a.pid2 = c.pid2 and a.flag = 1) t) as a
set @sql = @sql + ' from (select b.*,a.subname,c.score from subject a,student b,score c where b.empno = c.empno and a.pid1 = c.pid1 and a.pid2 = c.pid2 and a.flag = 1) t group by empno , name , dept'
exec(@sql)
/*
empno name dept 数学 物理 语文
---------- ---------- ---------- ----------- ----------- ---
001 张三 人事部 100 0 0
002 李四 会计部 0 0 95
008 王五 物流部 80 80 0
*/
drop table subject,student,score