数据排列问题，求SQL语句，在线等！

create table tb(no varchar(10) , name varchar(10) , date datetime)

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

create table tb(no varchar(10) , name varchar(10) , date datetime)

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 行受影响)

*/