11 2008-09-09 01:17:22.623
11 2008-09-06 13:44:40.060
11 2008-09-06 22:27:17.200
12 2008-09-07 13:10:15.123
12 2008-09-09 09:40:12.640
11 2008-09-07 15:35:24.513
11 2008-09-09 12:32:50.997
13 2008-09-09 17:43:22.513
如上为考勤时间,现想横向排列,
形成如下格式
2008-09-09 2008-09-10
11 上班 13:44:40.060
下班 22:27:17.200
语句应该怎么写,谢谢!
11 2008-09-06 13:44:40.060
11 2008-09-06 22:27:17.200
12 2008-09-07 13:10:15.123
12 2008-09-09 09:40:12.640
11 2008-09-07 15:35:24.513
11 2008-09-09 12:32:50.997
13 2008-09-09 17:43:22.513
如上为考勤时间,现想横向排列,
形成如下格式
2008-09-09 2008-09-10
11 上班 13:44:40.060
下班 22:27:17.200
语句应该怎么写,谢谢!
--参考这个
create table tb(部门ID int,部门名 varchar(20),门票ID int,门票名 varchar(20),总数 int,部门分配数 int)
insert into tb select 10, '董事会', 1, 'FB01A', 60, 5
insert into tb select 10, '董事会', 2, 'FB01B', 60, 0
insert into tb select 10, '董事会', 3, 'FB01C', 60, 0
insert into tb select 10, '董事会', 4, 'FB02A', 60, 8
insert into tb select 10, '董事会', 5, 'FB02B', 60, 0
insert into tb select 10, '董事会', 6, 'FB02C', 60, 2
insert into tb select 19, '营业', 1, 'FB01A', 60, 0
insert into tb select 19, '营业', 2, 'FB01B', 60, 3
insert into tb select 19, '营业', 3, 'FB01C', 60, 0
insert into tb select 19, '营业', 4, 'FB02A', 60, 8
insert into tb select 19, '营业', 5, 'FB02B', 60, 0
insert into tb select 19, '营业', 6, 'FB02C', 60, 0 declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when 部门名='''+部门名+''' then 部门分配数 else 0 end) ['+部门名+'(分配数)]'
from (select distinct 部门名 from tb ) a
exec ('select 门票ID,门票名,sum(总数) 总数'+@sql+' from tb group by 门票ID,门票名')drop table tb/*
门票ID 门票名 总数 董事会(分配数) 营业(分配数)
----------- -------------------- ----------- ----------- -----------
1 FB01A 120 5 0
2 FB01B 120 0 3
3 FB01C 120 0 0
4 FB02A 120 8 8
5 FB02B 120 0 0
6 FB02C 120 2 0
*/
哦,数据就是userid和时间了,上班,下班都是自己写的,就按12点前为上班,12点后为下班好了,
形成如下形式数据。 2008-09-09 2008-09-10
11 上班 10:44:40.060 10:44:40.060
下班 22:27:17.200 22:27:17.200
12 上班 9:27:17.200 9:27:17.200
下班 22:27:17.200 22:27:17.200
max(case when datename(hh , dt) <= 12 then right(convert(varchar(23),dt,120),12) else '' end) [上班],
max(case when datename(hh , dt) > 12 then right(convert(varchar(23),dt,120),12) else '' end) [下班]
from tb
group by id , convert(varchar(10),dt,120)