以下是我写的存贮过程,可以实现功能,就是汇总时速度太慢.所以问问大家的没有好的办法. 看看我的代码有什么问题,就是慢,功能可以实现-- 功能:月考勤汇总 -- 创建:2008-5-4 -- 更新:2008-5-6 13:52ALTER procedure Kq_YueHZ @yuefen char(6)='', --月份 @bumen char(2)='', --部门 @ltbj char(1)='' --临特标记 as set nocount on declare @NumberOfDays int --存放当月的天数 declare @i int --计数器 declare @riqi char(2) --存放日期 declare @strsql varchar(200)--生成临时表 --求出当月应有的天数 set @NumberOfDays=datediff(day,@yuefen+'01',dateadd(month,1,@yuefen+'01'))CREATE TABLE # ( 月份 CHAR(6), 部门 CHAR(2), 部门名称 VARCHAR(20), 编号 CHAR(5), 姓名 VARCHAR(10), FLAG INT, 项目 VARCHAR(10), [01号] VARCHAR(10) default '', [02号] VARCHAR(10) default '', [03号] VARCHAR(10) default '', [04号] VARCHAR(10) default '', [05号] VARCHAR(10) default '', [06号] VARCHAR(10) default '', [07号] VARCHAR(10) default '', [08号] VARCHAR(10) default '', [09号] VARCHAR(10) default '', [10号] VARCHAR(10) default '', [11号] VARCHAR(10) default '', [12号] VARCHAR(10) default '', [13号] VARCHAR(10) default '', [14号] VARCHAR(10) default '', [15号] VARCHAR(10) default '', [16号] VARCHAR(10) default '', [17号] VARCHAR(10) default '', [18号] VARCHAR(10) default '', [19号] VARCHAR(10) default '', [20号] VARCHAR(10) default '', [21号] VARCHAR(10) default '', [22号] VARCHAR(10) default '', [23号] VARCHAR(10) default '', [24号] VARCHAR(10) default '', [25号] VARCHAR(10) default '', [26号] VARCHAR(10) default '', [27号] VARCHAR(10) default '', [28号] VARCHAR(10) default '', [29号] VARCHAR(10) default '', [30号] VARCHAR(10) default '', [31号] VARCHAR(10) default '', [共计] VARCHAR(10) default '' ) --导入人员信息 select a.编号,max(a.姓名) as 姓名,max(a.部门) as 部门,max(b.部门名称) as 部门名称 into #yuangong from rikaoqin a left join yfbmda b on (a.部门=b.部门 and a.月份=b.月份) where a.月份=@yuefen and a.部门=@bumen and a.临特标记='' group by a.编号--导入班次信息 insert into # (月份,部门,部门名称,编号,姓名,flag,项目) select @yuefen as 月份,部门,部门名称,编号,姓名,1,'班次' from #yuangongcreate table #bc ( 编号 char(5), 姓名 varchar(10), 班次 varchar(10) )set @i=1 while @i<=@NumberOfDays begin set @riqi=right('0'+cast(@i as varchar),2) print @riqi insert #bc select 编号,max(姓名) as 姓名, case 班次 when 0 then '' when 1 then '白' when 2 then '夜' end as 班次 from rikaoqin where 月份=@yuefen and 日期=@riqi and 部门=@bumen and 临特标记=@ltbj group by 编号,日期,班次 set @strsql='update a set [' + @riqi + '号]=b.班次 from # a inner join #bc b on (a.编号=b.编号) where a.flag=1' print @strsql exec (@strsql) truncate table #bc set @i=@i+1 enddrop table #bc--导入工时信息 insert into # (月份,部门,部门名称,编号,姓名,flag,项目) select @yuefen as 月份,部门,部门名称,编号,姓名,2,'工时' from #yuangongcreate table #gs ( 编号 char(5), 姓名 varchar(10), 工时 varchar(10) )set @i=1 while @i<=@NumberOfDays begin set @riqi=right('0'+cast(@i as varchar),2) print @riqi insert #gs select 编号,max(姓名) as 姓名,isnull(工时,0) from rikaoqin where 月份=@yuefen and 日期=@riqi and 部门=@bumen and 临特标记=@ltbj group by 编号,日期,工时 set @strsql='update a set [' + @riqi + '号]=b.工时 from # a inner join #gs b on (a.编号=b.编号) where a.flag=2' print @strsql exec (@strsql) truncate table #gs set @i=@i+1 end--求总工时 select 编号,sum(isnull(工时,0)) as 总工时 into #zgs from rikaoqin where 月份=@yuefen and 部门=@bumen and 临特标记=@ltbj group by 编号update # set 共计=cast(b.总工时 as varchar) from # a inner join #zgs b on (a.编号=b.编号) where a.flag=2drop table #gs--导入加班工时信息 insert into # (月份,部门,部门名称,编号,姓名,flag,项目) select @yuefen as 月份,部门,部门名称,编号,姓名,3,'加班工时' from #yuangongcreate table #jbgs ( 编号 char(5), 姓名 varchar(10), 加班工时 varchar(10) )set @i=1 while @i<=@NumberOfDays begin set @riqi=right('0'+cast(@i as varchar),2) insert #jbgs select 编号,max(姓名) as 姓名,isnull(加班工时,0) from rikaoqin where 月份=@yuefen and 日期=@riqi and 部门=@bumen and 临特标记=@ltbj group by 编号,日期,加班工时 set @strsql='update a set [' + @riqi + '号]=b.加班工时 from # a inner join #jbgs b on (a.编号=b.编号) where a.flag=3' print @strsql exec (@strsql) truncate table #jbgs set @i=@i+1 end--求出总加班工时 select 编号,sum(isnull(加班工时,0)) as 总加班工时 into #zjbgs from rikaoqin where 月份=@yuefen and 部门=@bumen and 临特标记=@ltbj group by 编号update # set 共计=cast(b.总加班工时 as varchar) from # a inner join #zjbgs b on (a.编号=b.编号) where a.flag=3drop table #jbgs --导入缺勤原因信息 insert into # (月份,部门,部门名称,编号,姓名,flag,项目) select @yuefen as 月份,部门,部门名称,编号,姓名,4,'缺勤原因' from #yuangongcreate table #qq ( 编号 char(5), 姓名 varchar(10), 缺勤原因 varchar(10) default '', 休假时间 varchar(4) default '' )set @i=1 while @i<=@NumberOfDays begin set @riqi=right('0'+cast(@i as varchar),2) insert #qq select 编号,max(姓名) as 姓名, case 出勤编号 when 0 then '' when 1 then '事假' when 2 then '病假' when 3 then '公假' when 4 then '旷工' when 5 then '迟到' when 6 then '早退' when 7 then '加班' when 8 then '中夜班' when 9 then '探亲假' when 10 then '产假' when 11 then '工伤' when 12 then '年薪假' when 13 then '哺乳假' when 14 then '工休' when 15 then '倒休' end as 缺勤原因, case max(休假时间) when 'A' then '上午' when 'P' then '下午' else '' end as 休假时间 from rikaoqin where 月份=@yuefen and 日期=@riqi and 部门=@bumen and 临特标记=@ltbj group by 编号,日期,出勤编号 set @strsql='update a set [' + @riqi + '号]=rtrim(b.休假时间)+b.缺勤原因 from # a inner join #qq b on (a.编号=b.编号) where a.flag=4' print @strsql exec (@strsql) truncate table #qq set @i=@i+1 enddrop table #qqdrop table #yuangong select 编号,姓名,项目, [01号] as d1,[02号] as d2,[03号] as d3,[04号] as d4,[05号] as d5, [06号] as d6,[07号] as d7,[08号] as d8,[09号] as d9,[10号] as d10,[11号] as d11, [12号] as d12,[13号] as d13,[14号] as d14,[15号] as d15,[16号] as d16,[17号] as d17, [18号] as d18,[19号] as d19,[20号] as d20,[21号] as d21,[22号] as d22,[23号] as d23, [24号] as d24,[25号] as d25,[26号] as d26,[27号] as d27,[28号] as d28,[29号] as d29, [30号] as d30,[31号] as d31,共计, 月份,部门,部门名称,FLAG from # order by 编号,flag GO
create table cq(id int,yf char(6),rq char(2),bm int,hh int,bh char(3), xm varchar(10),bc int,gs int,jbgs int,cqbh int) insert cq select 1,'200805','01',17,1,'065','徐艳',1,10,0,0 insert cq select 1,'200805','02',17,1,'065','徐艳',1,10,0,0 insert cq select 1,'200805','31',17,1,'065','徐艳',2,10,0,0 insert cq select 2,'200805','01',17,4,'107','样利',1,0,0,0 insert cq select 2,'200805','02',17,4,'107','样利',0,0,0,1 insert cq select 2,'200805','31',17,4,'107','样利',0,0,0,1 go declare @yf char(6) set @yf = '200805' select bh,xm,item ,isnull(max(case rq when '01' then v end),'') as '[01号]' ,isnull(max(case rq when '02' then v end),'') as '[02号]' --... ,isnull(max(case rq when '31' then v end),'') as '[31号]' from ( select rq,bh,xm,item='班次',v=case bc when 1 then '白' when 2 then '夜' else '' end from cq where yf = @yf union select rq,bh,xm,item='工时',v=rtrim(gs) from cq where yf = @yf union select rq,bh,xm,item='加班工时',v=rtrim(jbgs) from cq where yf = @yf union select rq,bh,xm,item='缺勤原因' ,v=case cqbh when 0 then '' --... when 1 then '事假' end from cq where yf = @yf ) a group by bh,xm,itemgo drop table cq /* bh xm item [01号] [02号] [31号] ---- ---------- -------- ------------ ------------ ------------ 065 徐艳 班次 白 白 夜 065 徐艳 工时 10 10 10 065 徐艳 加班工时 0 0 0 065 徐艳 缺勤原因 107 样利 班次 白 107 样利 工时 0 0 0 107 样利 加班工时 0 0 0 107 样利 缺勤原因 事假 事假 */
谢谢cson_cson 您的代码我已采用。 再听听就结贴。另外,你真是csdn工作人员吗?
declare @yf char(6),@sql varchar(8000) set @yf = '200805' set @sql='select bh,xm,item,' select @sql=@sql+ 'isnull(max(case [rq] when '''+ cast( rq as varchar(50))+''' then v end),'' '') as [' + cast(rq as varchar(50))+'号],' from (select distinct rq from (select rq,bh,xm,item='班次',v=case bc when 1 then '白' when 2 then '夜' else '' end from cq where yf = @yf union select rq,bh,xm,item='工时',v=rtrim(gs) from cq where yf = @yf union select rq,bh,xm,item='加班工时',v=rtrim(jbgs) from cq where yf = @yf union select rq,bh,xm,item='缺勤原因' ,v=case cqbh when 0 then '' --... when 1 then '事假' end from cq where yf = @yf ) a )xx set @sql=left(@sql,len(@sql)-1)select @sql=@sql + 'from (select rq,bh,xm,item=''班次'',v=case bc when 1 then ''白'' when 2 then ''夜'' else '' '' end from cq where yf = ''200805'' union select rq,bh,xm,item=''工时'',v=rtrim(gs) from cq where yf = ''200805'' union select rq,bh,xm,item=''加班工时'',v=rtrim(jbgs) from cq where yf = ''200805'' union select rq,bh,xm,item=''缺勤原因'' ,v=case cqbh when 0 then '' '' --... when 1 then ''事假'' end from cq where yf ='' 200805 '' ) a group by bh,xm,item' exec (@sql)---------------- bh xm item 01号 02号 31号 ---- ---------- -------- ------------ ------------ ------------ 065 徐艳 班次 白 白 夜 065 徐艳 工时 10 10 10 065 徐艳 加班工时 0 0 0 107 样利 班次 白 107 样利 工时 0 0 0 107 样利 加班工时 0 0 0 警告: 聚合或其它 SET 操作消除了空值。(6 行受影响)
http://www.v2studio.cn/blog/gaojier/article.asp?id=71
[AutoID] [int] IDENTITY (1, 1) NOT NULL ,
[月份] [char] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[日期] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[部门] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[行号] [tinyint] NULL ,
[编号] [varchar] (5) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[姓名] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[班次] [tinyint] NULL CONSTRAINT [DF_RiKaoQin_班次] DEFAULT (0),
[工时] [numeric](5, 1) NULL CONSTRAINT [DF_RiKaoQin_工时] DEFAULT (0),
[加班工时] [numeric](5, 1) NULL CONSTRAINT [DF_RiKaoQin_加班工时] DEFAULT (0),
[出勤编号] [tinyint] NULL CONSTRAINT [DF_RiKaoQin_休假编号] DEFAULT (0),
[休假时间] [char] (1) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_RiKaoQin_休假时间] DEFAULT (''),
[备注] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_RiKaoQin_备注] DEFAULT (''),
[临特标记] [char] (1) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_RiKaoQin_临特标记] DEFAULT (''),
[审核人] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[审核时间] [datetime] NULL ,
[插入记录] [datetime] NULL CONSTRAINT [DF_RiKaoQin_插入记录] DEFAULT (getdate()),
CONSTRAINT [PK_RiKaoQin] PRIMARY KEY CLUSTERED
(
[AutoID]
) ON [PRIMARY] ,
CONSTRAINT [CK_RiKaoQin] CHECK ([月份] <> '' and [日期] <> '' and [部门] <> '' and [编号] <> '' and [姓名] <> '')
) ON [PRIMARY]
GO
看看我的代码有什么问题,就是慢,功能可以实现-- 功能:月考勤汇总
-- 创建:2008-5-4
-- 更新:2008-5-6 13:52ALTER procedure Kq_YueHZ
@yuefen char(6)='', --月份
@bumen char(2)='', --部门
@ltbj char(1)='' --临特标记
as
set nocount on
declare @NumberOfDays int --存放当月的天数
declare @i int --计数器
declare @riqi char(2) --存放日期
declare @strsql varchar(200)--生成临时表
--求出当月应有的天数
set @NumberOfDays=datediff(day,@yuefen+'01',dateadd(month,1,@yuefen+'01'))CREATE TABLE # (
月份 CHAR(6),
部门 CHAR(2),
部门名称 VARCHAR(20),
编号 CHAR(5),
姓名 VARCHAR(10),
FLAG INT,
项目 VARCHAR(10),
[01号] VARCHAR(10) default '',
[02号] VARCHAR(10) default '',
[03号] VARCHAR(10) default '',
[04号] VARCHAR(10) default '',
[05号] VARCHAR(10) default '',
[06号] VARCHAR(10) default '',
[07号] VARCHAR(10) default '',
[08号] VARCHAR(10) default '',
[09号] VARCHAR(10) default '',
[10号] VARCHAR(10) default '',
[11号] VARCHAR(10) default '',
[12号] VARCHAR(10) default '',
[13号] VARCHAR(10) default '',
[14号] VARCHAR(10) default '',
[15号] VARCHAR(10) default '',
[16号] VARCHAR(10) default '',
[17号] VARCHAR(10) default '',
[18号] VARCHAR(10) default '',
[19号] VARCHAR(10) default '',
[20号] VARCHAR(10) default '',
[21号] VARCHAR(10) default '',
[22号] VARCHAR(10) default '',
[23号] VARCHAR(10) default '',
[24号] VARCHAR(10) default '',
[25号] VARCHAR(10) default '',
[26号] VARCHAR(10) default '',
[27号] VARCHAR(10) default '',
[28号] VARCHAR(10) default '',
[29号] VARCHAR(10) default '',
[30号] VARCHAR(10) default '',
[31号] VARCHAR(10) default '',
[共计] VARCHAR(10) default ''
)
--导入人员信息
select a.编号,max(a.姓名) as 姓名,max(a.部门) as 部门,max(b.部门名称) as 部门名称
into #yuangong
from rikaoqin a left join yfbmda b
on (a.部门=b.部门 and a.月份=b.月份)
where a.月份=@yuefen and a.部门=@bumen and a.临特标记=''
group by a.编号--导入班次信息
insert into #
(月份,部门,部门名称,编号,姓名,flag,项目)
select @yuefen as 月份,部门,部门名称,编号,姓名,1,'班次'
from #yuangongcreate table #bc (
编号 char(5),
姓名 varchar(10),
班次 varchar(10)
)set @i=1
while @i<=@NumberOfDays
begin
set @riqi=right('0'+cast(@i as varchar),2) print @riqi insert #bc
select 编号,max(姓名) as 姓名,
case 班次
when 0 then ''
when 1 then '白'
when 2 then '夜'
end as 班次
from rikaoqin
where 月份=@yuefen
and 日期=@riqi
and 部门=@bumen
and 临特标记=@ltbj
group by 编号,日期,班次 set @strsql='update a set [' + @riqi + '号]=b.班次 from # a inner join #bc b on (a.编号=b.编号) where a.flag=1' print @strsql exec (@strsql) truncate table #bc
set @i=@i+1
enddrop table #bc--导入工时信息
insert into #
(月份,部门,部门名称,编号,姓名,flag,项目)
select @yuefen as 月份,部门,部门名称,编号,姓名,2,'工时'
from #yuangongcreate table #gs (
编号 char(5),
姓名 varchar(10),
工时 varchar(10)
)set @i=1
while @i<=@NumberOfDays
begin
set @riqi=right('0'+cast(@i as varchar),2) print @riqi insert #gs
select 编号,max(姓名) as 姓名,isnull(工时,0)
from rikaoqin
where 月份=@yuefen
and 日期=@riqi
and 部门=@bumen
and 临特标记=@ltbj
group by 编号,日期,工时 set @strsql='update a set [' + @riqi + '号]=b.工时 from # a inner join #gs b on (a.编号=b.编号) where a.flag=2' print @strsql exec (@strsql) truncate table #gs
set @i=@i+1
end--求总工时
select 编号,sum(isnull(工时,0)) as 总工时
into #zgs
from rikaoqin
where 月份=@yuefen and 部门=@bumen and 临特标记=@ltbj
group by 编号update #
set 共计=cast(b.总工时 as varchar)
from # a inner join #zgs b on (a.编号=b.编号)
where a.flag=2drop table #gs--导入加班工时信息
insert into #
(月份,部门,部门名称,编号,姓名,flag,项目)
select @yuefen as 月份,部门,部门名称,编号,姓名,3,'加班工时'
from #yuangongcreate table #jbgs (
编号 char(5),
姓名 varchar(10),
加班工时 varchar(10)
)set @i=1
while @i<=@NumberOfDays
begin
set @riqi=right('0'+cast(@i as varchar),2) insert #jbgs
select 编号,max(姓名) as 姓名,isnull(加班工时,0)
from rikaoqin
where 月份=@yuefen
and 日期=@riqi
and 部门=@bumen
and 临特标记=@ltbj
group by 编号,日期,加班工时 set @strsql='update a set [' + @riqi + '号]=b.加班工时 from # a inner join #jbgs b on (a.编号=b.编号) where a.flag=3' print @strsql exec (@strsql) truncate table #jbgs
set @i=@i+1
end--求出总加班工时
select 编号,sum(isnull(加班工时,0)) as 总加班工时
into #zjbgs
from rikaoqin
where 月份=@yuefen and 部门=@bumen and 临特标记=@ltbj
group by 编号update #
set 共计=cast(b.总加班工时 as varchar)
from # a inner join #zjbgs b on (a.编号=b.编号)
where a.flag=3drop table #jbgs
--导入缺勤原因信息
insert into #
(月份,部门,部门名称,编号,姓名,flag,项目)
select @yuefen as 月份,部门,部门名称,编号,姓名,4,'缺勤原因'
from #yuangongcreate table #qq (
编号 char(5),
姓名 varchar(10),
缺勤原因 varchar(10) default '',
休假时间 varchar(4) default ''
)set @i=1
while @i<=@NumberOfDays
begin
set @riqi=right('0'+cast(@i as varchar),2) insert #qq
select 编号,max(姓名) as 姓名,
case 出勤编号
when 0 then ''
when 1 then '事假'
when 2 then '病假'
when 3 then '公假'
when 4 then '旷工'
when 5 then '迟到'
when 6 then '早退'
when 7 then '加班'
when 8 then '中夜班'
when 9 then '探亲假'
when 10 then '产假'
when 11 then '工伤'
when 12 then '年薪假'
when 13 then '哺乳假'
when 14 then '工休'
when 15 then '倒休'
end as 缺勤原因,
case max(休假时间)
when 'A' then '上午'
when 'P' then '下午'
else ''
end as 休假时间
from rikaoqin
where 月份=@yuefen
and 日期=@riqi
and 部门=@bumen
and 临特标记=@ltbj
group by 编号,日期,出勤编号 set @strsql='update a set [' + @riqi + '号]=rtrim(b.休假时间)+b.缺勤原因 from # a inner join #qq b on (a.编号=b.编号) where a.flag=4' print @strsql exec (@strsql) truncate table #qq
set @i=@i+1
enddrop table #qqdrop table #yuangong
select
编号,姓名,项目,
[01号] as d1,[02号] as d2,[03号] as d3,[04号] as d4,[05号] as d5,
[06号] as d6,[07号] as d7,[08号] as d8,[09号] as d9,[10号] as d10,[11号] as d11,
[12号] as d12,[13号] as d13,[14号] as d14,[15号] as d15,[16号] as d16,[17号] as d17,
[18号] as d18,[19号] as d19,[20号] as d20,[21号] as d21,[22号] as d22,[23号] as d23,
[24号] as d24,[25号] as d25,[26号] as d26,[27号] as d27,[28号] as d28,[29号] as d29,
[30号] as d30,[31号] as d31,共计,
月份,部门,部门名称,FLAG
from #
order by 编号,flag
GO
xm varchar(10),bc int,gs int,jbgs int,cqbh int)
insert cq select 1,'200805','01',17,1,'065','徐艳',1,10,0,0
insert cq select 1,'200805','02',17,1,'065','徐艳',1,10,0,0
insert cq select 1,'200805','31',17,1,'065','徐艳',2,10,0,0
insert cq select 2,'200805','01',17,4,'107','样利',1,0,0,0
insert cq select 2,'200805','02',17,4,'107','样利',0,0,0,1
insert cq select 2,'200805','31',17,4,'107','样利',0,0,0,1
go
declare @yf char(6)
set @yf = '200805'
select bh,xm,item
,isnull(max(case rq when '01' then v end),'') as '[01号]'
,isnull(max(case rq when '02' then v end),'') as '[02号]'
--...
,isnull(max(case rq when '31' then v end),'') as '[31号]'
from (
select rq,bh,xm,item='班次',v=case bc when 1 then '白' when 2 then '夜' else '' end
from cq where yf = @yf union
select rq,bh,xm,item='工时',v=rtrim(gs)
from cq where yf = @yf union
select rq,bh,xm,item='加班工时',v=rtrim(jbgs)
from cq where yf = @yf union
select rq,bh,xm,item='缺勤原因'
,v=case cqbh
when 0 then ''
--...
when 1 then '事假' end
from cq where yf = @yf
) a group by bh,xm,itemgo
drop table cq
/*
bh xm item [01号] [02号] [31号]
---- ---------- -------- ------------ ------------ ------------
065 徐艳 班次 白 白 夜
065 徐艳 工时 10 10 10
065 徐艳 加班工时 0 0 0
065 徐艳 缺勤原因
107 样利 班次 白
107 样利 工时 0 0 0
107 样利 加班工时 0 0 0
107 样利 缺勤原因 事假 事假
*/
您的代码我已采用。
再听听就结贴。另外,你真是csdn工作人员吗?
set @yf = '200805'
set @sql='select bh,xm,item,'
select @sql=@sql+
'isnull(max(case [rq] when '''+ cast( rq as varchar(50))+''' then v end),'' '') as [' + cast(rq as varchar(50))+'号],'
from (select distinct rq from
(select rq,bh,xm,item='班次',v=case bc when 1 then '白' when 2 then '夜' else '' end
from cq where yf = @yf union
select rq,bh,xm,item='工时',v=rtrim(gs)
from cq where yf = @yf union
select rq,bh,xm,item='加班工时',v=rtrim(jbgs)
from cq where yf = @yf union
select rq,bh,xm,item='缺勤原因'
,v=case cqbh
when 0 then ''
--...
when 1 then '事假' end
from cq where yf = @yf
) a
)xx
set @sql=left(@sql,len(@sql)-1)select @sql=@sql +
'from
(select rq,bh,xm,item=''班次'',v=case bc when 1 then ''白'' when 2 then ''夜'' else '' '' end
from cq where yf = ''200805'' union
select rq,bh,xm,item=''工时'',v=rtrim(gs)
from cq where yf = ''200805'' union
select rq,bh,xm,item=''加班工时'',v=rtrim(jbgs)
from cq where yf = ''200805'' union
select rq,bh,xm,item=''缺勤原因''
,v=case cqbh
when 0 then '' ''
--...
when 1 then ''事假'' end
from cq where yf ='' 200805 ''
) a group by bh,xm,item'
exec (@sql)----------------
bh xm item 01号 02号 31号
---- ---------- -------- ------------ ------------ ------------
065 徐艳 班次 白 白 夜
065 徐艳 工时 10 10 10
065 徐艳 加班工时 0 0 0
107 样利 班次 白
107 样利 工时 0 0 0
107 样利 加班工时 0 0 0
警告: 聚合或其它 SET 操作消除了空值。(6 行受影响)