表Ano name date
---------------------------------------
0001 张三 2007-11-01 07:52:13.000
0001 张三 2007-11-01 12:04:26.000
0001 张三 2007-11-01 12:10:10.000
0001 张三 2007-11-01 17:04:33.000
0001 张三 2007-11-02 07:41:44.000
0002 李四 2007-11-02 08:09:56.000
0002 李四 2007-11-02 12:15:48.000
0001 张三 2007-11-02 12:16:59.000
0001 张三 2007-11-02 12:25:32.000
0002 李四 2007-11-02 12:25:59.000
0002 李四 2007-11-02 17:32:18.000
0001 张三 2007-11-02 17:38:54.000
0001 张三 2007-11-03 07:47:25.000
0002 李四 2007-11-03 08:17:10.000
0001 张三 2007-11-03 12:01:46.000
0001 张三 2007-11-03 12:05:07.000
0002 李四 2007-11-03 12:36:57.000
0002 李四 2007-11-03 12:52:22.000
0001 张三 2007-11-03 17:03:12.000
0002 李四 2007-11-03 17:33:51.000如何通过SQL语句查询到如下结果:no name date t1 t2 t3 t4 ... t10
---------------------------------------------------------------
0001 张三 2007-11-01 07:52 12:04 12:10 17:04
2007-11-02 07:41 12:16 12:25 17:38
2007-11-03 07:47 12:01 12:05 17:03
0002 李四 2007-11-02 08:09 12:15 12:25 17:32
2007-11-03 08:17 12:36 12:52 17:33
---------------------------------------
0001 张三 2007-11-01 07:52:13.000
0001 张三 2007-11-01 12:04:26.000
0001 张三 2007-11-01 12:10:10.000
0001 张三 2007-11-01 17:04:33.000
0001 张三 2007-11-02 07:41:44.000
0002 李四 2007-11-02 08:09:56.000
0002 李四 2007-11-02 12:15:48.000
0001 张三 2007-11-02 12:16:59.000
0001 张三 2007-11-02 12:25:32.000
0002 李四 2007-11-02 12:25:59.000
0002 李四 2007-11-02 17:32:18.000
0001 张三 2007-11-02 17:38:54.000
0001 张三 2007-11-03 07:47:25.000
0002 李四 2007-11-03 08:17:10.000
0001 张三 2007-11-03 12:01:46.000
0001 张三 2007-11-03 12:05:07.000
0002 李四 2007-11-03 12:36:57.000
0002 李四 2007-11-03 12:52:22.000
0001 张三 2007-11-03 17:03:12.000
0002 李四 2007-11-03 17:33:51.000如何通过SQL语句查询到如下结果:no name date t1 t2 t3 t4 ... t10
---------------------------------------------------------------
0001 张三 2007-11-01 07:52 12:04 12:10 17:04
2007-11-02 07:41 12:16 12:25 17:38
2007-11-03 07:47 12:01 12:05 17:03
0002 李四 2007-11-02 08:09 12:15 12:25 17:32
2007-11-03 08:17 12:36 12:52 17:33
insert into tb values('0001', '张三', '2007-11-01 07:52:13.000')
insert into tb values('0001', '张三', '2007-11-01 12:04:26.000')
insert into tb values('0001', '张三', '2007-11-01 12:10:10.000')
insert into tb values('0001', '张三', '2007-11-01 17:04:33.000')
insert into tb values('0001', '张三', '2007-11-02 07:41:44.000')
insert into tb values('0002', '李四', '2007-11-02 08:09:56.000')
insert into tb values('0002', '李四', '2007-11-02 12:15:48.000')
insert into tb values('0001', '张三', '2007-11-02 12:16:59.000')
insert into tb values('0001', '张三', '2007-11-02 12:25:32.000')
insert into tb values('0002', '李四', '2007-11-02 12:25:59.000')
insert into tb values('0002', '李四', '2007-11-02 17:32:18.000')
insert into tb values('0001', '张三', '2007-11-02 17:38:54.000')
insert into tb values('0001', '张三', '2007-11-03 07:47:25.000')
insert into tb values('0002', '李四', '2007-11-03 08:17:10.000')
insert into tb values('0001', '张三', '2007-11-03 12:01:46.000')
insert into tb values('0001', '张三', '2007-11-03 12:05:07.000')
insert into tb values('0002', '李四', '2007-11-03 12:36:57.000')
insert into tb values('0002', '李四', '2007-11-03 12:52:22.000')
insert into tb values('0001', '张三', '2007-11-03 17:03:12.000')
insert into tb values('0002', '李四', '2007-11-03 17:33:51.000')
go
select no , name , convert(varchar(10),date,120) date,
max(case px when 1 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T1',
max(case px when 2 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T2',
max(case px when 3 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T3',
max(case px when 4 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T4',
max(case px when 5 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T5',
max(case px when 6 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T6',
max(case px when 7 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T7',
max(case px when 8 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T8',
max(case px when 9 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T9',
max(case px when 10 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T10'
from
(
select * , px = (select count(1) from tb where no = a.no and name = a.name and convert(varchar(10),date,120) = convert(varchar(10),a.date,120) and date < a.date) + 1 from tb a
) t
group by no , name , convert(varchar(10),date,120)drop table tb/*
no name date T1 T2 T3 T4 T5 T6 T7 T8 T9 T10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0001 张三 2007-11-01 07:52 12:04 12:10 17:04
0001 张三 2007-11-02 07:41 12:16 12:25 17:38
0001 张三 2007-11-03 07:47 12:01 12:05 17:03
0002 李四 2007-11-02 08:09 12:15 12:25 17:32
0002 李四 2007-11-03 08:17 12:36 12:52 17:33 (5 行受影响)
*/
insert into tb values('0001', '张三', '2007-11-01 07:52:13.000')
insert into tb values('0001', '张三', '2007-11-01 12:04:26.000')
insert into tb values('0001', '张三', '2007-11-01 12:10:10.000')
insert into tb values('0001', '张三', '2007-11-01 17:04:33.000')
insert into tb values('0001', '张三', '2007-11-02 07:41:44.000')
insert into tb values('0002', '李四', '2007-11-02 08:09:56.000')
insert into tb values('0002', '李四', '2007-11-02 12:15:48.000')
insert into tb values('0001', '张三', '2007-11-02 12:16:59.000')
insert into tb values('0001', '张三', '2007-11-02 12:25:32.000')
insert into tb values('0002', '李四', '2007-11-02 12:25:59.000')
insert into tb values('0002', '李四', '2007-11-02 17:32:18.000')
insert into tb values('0001', '张三', '2007-11-02 17:38:54.000')
insert into tb values('0001', '张三', '2007-11-03 07:47:25.000')
insert into tb values('0002', '李四', '2007-11-03 08:17:10.000')
insert into tb values('0001', '张三', '2007-11-03 12:01:46.000')
insert into tb values('0001', '张三', '2007-11-03 12:05:07.000')
insert into tb values('0002', '李四', '2007-11-03 12:36:57.000')
insert into tb values('0002', '李四', '2007-11-03 12:52:22.000')
insert into tb values('0001', '张三', '2007-11-03 17:03:12.000')
insert into tb values('0002', '李四', '2007-11-03 17:33:51.000')
go--静态SQL,指一个人每天的时间数量确定,如最多为10个。
select no , name , convert(varchar(10),date,120) date,
max(case px when 1 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T1',
max(case px when 2 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T2',
max(case px when 3 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T3',
max(case px when 4 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T4',
max(case px when 5 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T5',
max(case px when 6 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T6',
max(case px when 7 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T7',
max(case px when 8 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T8',
max(case px when 9 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T9',
max(case px when 10 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T10'
from
(
select * , px = (select count(1) from tb where no = a.no and name = a.name and convert(varchar(10),date,120) = convert(varchar(10),a.date,120) and date < a.date) + 1 from tb a
) t
group by no , name , convert(varchar(10),date,120)
/*
no name date T1 T2 T3 T4 T5 T6 T7 T8 T9 T10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0001 张三 2007-11-01 07:52 12:04 12:10 17:04
0001 张三 2007-11-02 07:41 12:16 12:25 17:38
0001 张三 2007-11-03 07:47 12:01 12:05 17:03
0002 李四 2007-11-02 08:09 12:15 12:25 17:32
0002 李四 2007-11-03 08:17 12:36 12:52 17:33 (5 行受影响)
*/--动态SQL,指一个人每天的时间数量不确定
declare @sql varchar(8000)
set @sql = 'select no , name , convert(varchar(10),date,120) date'
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then substring(convert(varchar(20),date,120),12,5) else '' '' end) [T' + cast(px as varchar) + ']'
from (select distinct px from (select * , px = (select count(1) from tb where no = a.no and name = a.name and convert(varchar(10),date,120) = convert(varchar(10),a.date,120) and date < a.date) + 1 from tb a) t) as a
set @sql = @sql + ' from (select * , px = (select count(1) from tb where no = a.no and name = a.name and convert(varchar(10),date,120) = convert(varchar(10),a.date,120) and date < a.date) + 1 from tb a) t group by no , name , convert(varchar(10),date,120)'
exec(@sql)
/*
no name date T1 T2 T3 T4
---------- ---------- ---------- ---------- ---------- ---------- ----------
0001 张三 2007-11-01 07:52 12:04 12:10 17:04
0001 张三 2007-11-02 07:41 12:16 12:25 17:38
0001 张三 2007-11-03 07:47 12:01 12:05 17:03
0002 李四 2007-11-02 08:09 12:15 12:25 17:32
0002 李四 2007-11-03 08:17 12:36 12:52 17:33(5 行受影响)
*/drop table tb