SQL Query using pivot and group by on SQL 2008 -

i have punch clock database in sql 2008 , select data results using pivot , group date (not time) , name. 1 name, want have punchtime of same day in same row.

original table                  result table             | name   | punchtime         |  | name   | time1             | time2             | time3             | time4             | ------------------------------- ------------------------------------------------------------------------------------------ |   john |2015-06-01 10:00:00|  |   john |2015-06-01 10:00:00|2015-06-01 12:00:00|2015-06-01 13:00:00|2015-06-01 21:00:00| |   john |2015-05-30 21:34:27|  |   amy  |2015-06-01 11:14:00|2015-06-01 17:00:00|                   |                   | |   john |2015-06-01 10:00:00|  |   amy  |2015-06-02 09:15:00|2015-06-02 12:25:00|                   |                   | |    amy |2015-06-01 11:14:00|  |   amy  |2015-06-03 17:35:00|                   |                   |                   | |   john |2015-06-01 12:00:00|  |   john |2015-05-30 09:04:27|2015-05-30 21:34:27|                   |                   | |   john |2015-06-01 13:00:00| |    amy |2015-06-01 17:00:00| |   john |2015-06-01 21:00:00| |    amy |2015-06-02 09:15:00| |    amy |2015-06-02 12:25:00| |    amy |2015-06-03 17:35:00| 

try :-

set nocount on;  declare  @punchcount        int = 0         ,@pivotcolumns      varchar(max)         ,@sql               varchar(max)  select   @pivotcolumns = ''         ,@sql = ''  if object_id('tempdb.dbo.#punches') not null drop table #punches;  create table #punches (      rowid          int identity(1,1) primary key     ,name           varchar(100)     ,punchtime      datetime )  insert #punches(name, punchtime) values  ('john','2015-06-01 10:00:00') ,('john','2015-05-30 21:34:27') ,('john','2015-06-01 10:00:00') ,('amy','2015-06-01 11:14:00') ,('john','2015-06-01 12:00:00') ,('john','2015-06-01 13:00:00') ,('amy','2015-06-01 17:00:00') ,('john','2015-06-01 21:00:00') ,('amy','2015-06-02 09:15:00') ,('amy','2015-06-02 12:25:00') ,('amy','2015-06-03 17:35:00')  select  @punchcount = max(t.punchcount)    (             select   t.name                     ,dateadd(d, 0, datediff(d, 0, t.punchtime)) punchdate                     ,count(t.punchtime) punchcount                #punches t             group t.name                     ,dateadd(d, 0, datediff(d, 0, t.punchtime))         ) t  ;with pc (     select  1 punchcount      union      select  (punchcount + 1)        pc       punchcount < @punchcount )  select  @pivotcolumns = @pivotcolumns + (t.timecol + cast(pc.punchcount varchar(10)) + ',')    pc         cross join         (             select  'time' timecol         ) t  select  @sql = 'select  name,' + substring(@pivotcolumns,0,len(@pivotcolumns)) + '  ( ' +         ' select     p.name ' +                     ' ,p.punchtime ' +                     ' ,dateadd(d, 0, datediff(d, 0, p.punchtime)) punchdate ' +                     ' ,(''time'' + cast((row_number() on (partition p.name,dateadd(d, 0, datediff(d, 0, p.punchtime)) order p.name,dateadd(d, 0, datediff(d, 0, p.punchtime)),p.punchtime)) varchar(10))) ' +                     '  rownum ' +         '  #punches p ' +         ' ) s ' +     ' pivot ' +     ' ( ' +     '   max(punchtime) ' +     '   rownum in (' + substring(@pivotcolumns,0,len(@pivotcolumns)) + ') ' +     ' ) p '  ----print (@sql) exec (@sql)  go