34,590
社区成员
发帖
与我相关
我的任务
分享
create table test(Date SMALLDATETIME,NAME VARCHAR(20),AMSign SMALLDATETIME,PMSign SMALLDATETIME)
insert test select '2009-02-09 0:00:00', '张三','2009-02-09 8:33:16', '2009-02-09 14:03:13'
union all select '2009-02-10 0:00:00', '张三' ,'2009-02-10 8:30:44', NULL
union all select '2009-02-09 0:00:00', '李四' ,'2009-02-09 8:33:16','2009-02-09 14:03:13'
union all select '2009-02-10 0:00:00' ,'李四' ,NULL,NULL
DECLARE @sql VARCHAR(2000)
SELECT @sql=ISNULL(@sql+',','')+' max(case when convert(varchar(10),date,120)='''+a+''' and AMSign is null then ''未确认'' else ''确认'' end) ['+a+'AM],'+' max(case when convert(varchar(10),date,120)='''+a+''' and PMSign is null then ''未确认'' else ''确认'' end) ['+a+'PM]' FROM (select DISTINCT convert(varchar(10),[date],120) a FROM test )aa
EXEC('select name,'+@sql+' from test group by name')
-- 可能某天所有人都没有记录, 所以比较保险的是用循环
DECLARE
@s nvarchar(4000),
@dt1 datetime,
@dt2 datetime;
SELECT
@s = N'',
@dt1 = CASE
WHEN MIN(AMSign) < MIN(PMSign) THEN MIN(AMSign)
ELSE MIN(PMSign)
END,
@dt2 = CASE
WHEN MAX(AMSign) < MAX(PMSign) THEN MAX(AMSign)
ELSE MAX(PMSign)
END;
WHILE DATEDIFF(Day, @dt1, @dt2) >= 0
BEGIN
SELECT
@s = @s
+ N',
[' + CONVERT(varchar(10), @dt1, 120) + N'AM] = CASE
WHEN CONVERT(char(10), MAX(AMSign), 120) = ''' + ' + CONVERT(varchar(10), @dt1, 120) + N'''' = THEN N''确认''
ELSE N''未确认''
END,
[' + CONVERT(varchar(10), @dt1, 120) + N'PM] = CASE
WHEN CONVERT(char(10), MAX(PMSign), 120) = ''' + ' + CONVERT(varchar(10), @dt1, 120) + N'''' = THEN N''确认''
ELSE N'未确认'
END',
@dt1 = DATEADD(Day, 1, @dt1);
END;
EXEC(N'
SELECT
Name' + @s + N'
FROM TB
GROUP BY Name
');
create table test(Date datetime,Name varchar(10),AMSign datetime,PMSign datetime)
insert into test select '2009-02-09 0:00:00','张三','2009-02-09 8:33:16','2009-02-09 14:03:13'
insert into test select '2009-02-10 0:00:00','张三','2009-02-10 8:30:44',NULL
insert into test select '2009-02-09 0:00:00','李四','2009-02-09 8:33:16','2009-02-09 14:03:13'
insert into test select '2009-02-10 0:00:00','李四',NULL ,NULL
go
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',['+Date+'AM]=isnull(max(case when convert(char(10),Date,120)='''+Date+''' and AMSign is not null then ''确认 '' end),''未确认'')'
+',['+Date+'PM]=isnull(max(case when convert(char(10),Date,120)='''+Date+''' and PMSign is not null then ''确认 '' end),''未确认'')'
from (select distinct convert(char(10),Date,120) as Date from test) t
set @sql='select Name'+@sql+' from test group by Name'
exec(@sql)
/*
Name 2009-02-09AM 2009-02-09PM 2009-02-10AM 2009-02-10PM
---------- ------------ ------------ ------------ ------------
李四 确认 确认 未确认 未确认
张三 确认 确认 确认 未确认
*/
go
drop table test
go