27,579
社区成员
发帖
与我相关
我的任务
分享
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([RmNum] INT,[Day] INT,[Stdt_tm] NVARCHAR(10),[Eddt_tm] NVARCHAR(10),[Zao] INT,[Zhong] INT,[Wan] INT)
INSERT [tb]
SELECT 4,12,'14:55','10:19',0,1,0 UNION ALL
SELECT 4,13,'01:20','03:20',0,0,1 UNION ALL
SELECT 6,13,'14:46','10:10',0,1,0 UNION ALL
SELECT 8,14,'11:26','10:19',0,1,0 UNION ALL
SELECT 8,15,'11:26','10:21',0,1,1 UNION ALL
SELECT 9,16,'15:26','14:41',0,1,1 UNION ALL
SELECT 11,16,'13:06','10:19',1,1,0 UNION ALL
SELECT 11,18,'11:31','11:44',1,1,0 UNION ALL
SELECT 12,19,'11:56','15:45',0,1,1 UNION ALL
SELECT 12,20,'15:45','15:49',0,1,0
GO
--SELECT * FROM [tb]
-->SQL查询如下:
declare @s varchar(8000)
select @s='select [RmNum]'
select @s=@s+',max(case [Day] when '+LTRIM([day]) +' then [Stdt_tm] else '''' end)['+LTRIM([day])+'日S]'
+',max(case [Day] when '+LTRIM([day]) +' then [Eddt_tm] else '''' end)['+LTRIM([day])+'日E]'
--......其它的自己举一反三
from tb
group by Day
select @s=@s+' from tb group by RmNum'
exec (@s)
/*
RmNum 12日S 12日E 13日S 13日E 14日S 14日E 15日S 15日E 16日S 16日E 18日S 18日E 19日S 19日E 20日S 20日E
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
4 14:55 10:19 01:20 03:20
6 14:46 10:10
8 11:26 10:19 11:26 10:21
9 15:26 14:41
11 13:06 10:19 11:31 11:44
12 11:56 15:45 15:45 15:49
(6 行受影响)
*/