求行转列问题,看了很多但还是不太明白
数据结构:
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 ...
各位,欢迎解答.
[解决办法]
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张三人事部10000
002李四会计部0095
008王五物流部80800
*/
[解决办法]
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 行受影响)
[解决办法]
顶!
