交叉表

 

Ø       交叉表问题

现有两个表:资料如下

一.部门表

部门编码	部门名称	级别
01	管理部	1
0101	行政部	2
02	生产部	1
0201	一车间	2

二.出勤表

日期	部门	总人数	出勤人数
2003-03-01	行政部	50	45
2003-03-01	一车间	500	490
2003-03-02	行政部	50	45
2003-03-02	一车间	500	490
2003-03-03	行政部	50	49
2003-03-03	一车间	500	498

现在要得出的结果是如下:

日期	行政部 总人数	行政部 出勤人数	一车间 总人数	一车间 出勤人数
2003-03-01	50	45	500	490
2003-03-02	50	45	500	490
2003-03-02	50	49	500	498

请问如果用SQL语句实现？

Ø         解决问题：

-- Create two temp tables for test

create table #Department (code varchar(100),name varchar(100),grade int)
insert  #Department values('01','管理部',    1   )
insert  #Department values('0101',     '行政部',      2)
insert  #Department values('02'   ,    '生产部' ,     1)
insert  #Department values('0201'  ,   '一车间'  ,    2)

create table #DutyLog(logDate datetime,department varchar(100),totalNumber int,onDutyNumber int)
insert #DutyLog values('2003-03-01','行政部',   50 ,     45)
insert #DutyLog values('2003-03-01','一车间' ,  500 ,    490)
insert #DutyLog values('2003-03-02','行政部'  , 50   ,   45)
insert #DutyLog values('2003-03-02','一车间'   ,500   ,  490)
insert #DutyLog values('2003-03-03','行政部'  , 50     , 49)
insert #DutyLog values('2003-03-03','一车间',   500 ,    498)

 

-- Solution 1 --
declare @sql varchar(8000)
set @sql=''

---如果没有出勤也要列出：
select @sql=@sql+',sum(case when department='''+name+''' then totalNumber else 0 end) '+name+'totalNumber,sum(case when department='''+name+''' then onDutyNumber else 0 end) as '+name+'onDutyNumber' from #Department
exec ('select logDate'+@sql+' from #DutyLog group by logDate order by logDate')

--如果不列出可以这样：
set @sql=''
select @sql=@sql+',sum(case when department='''+department+''' then totalNumber else 0 end) '+department+'totalNumber,sum(case when department='''+department+''' then onDutyNumber else 0 end) as '+department+'onDutyNumber' from (select distinct department from #DutyLog) aa
exec ('select logDate'+@sql+' from #DutyLog group by logDate order by logDate')
go

-- Solution 2 --
-- 可以不用department表
declare @sql varchar(8000)
set @sql=''
select distinct department into #temp_tbl from #DutyLog
select @sql=@sql+',sum(case when department='''+rtrim(department)+''' then totalNumber else 0 end) as '+rtrim(department)+'totalNumber,sum(case when department='''+rtrim(department)+''' then onDutyNumber else 0 end) as '+rtrim(department)+'onDutyNumber' from #temp_tbl
set @sql='select logDate'+@sql+' from #DutyLog group by logDate order by logDate'
exec (@sql)

 Go

-- Drop two temp tables after test

drop table #Department,#DutyLog