how do pivot for number of punch Times in a day in sql server 2005?

Question

CREATE TABLE ALL_PUNCHES_PIVOT2 (ID INT,strcardid NVARCHAR(100),SwipeDate VARCHAR(60),TIME VARCHAR(40));

INSERT INTO ALL_PUNCHES_PIVOT2 (ID,strcardid,SwipeDate,PUNCHTIME)
VALUES (1,'3716817970','01/08/2013','08:47:53')
INSERT INTO ALL_PUNCHES_PIVOT2 (ID,strcardid,SwipeDate,PUNCHTIME)
VALUES (2,'3716817970','01/08/2013','08:47:56')
INSERT INTO ALL_PUNCHES_PIVOT2 (ID,strcardid,SwipeDate,PUNCHTIME)
VALUES (3,'3716817970','01/08/2013','08:52:29')
INSERT INTO ALL_PUNCHES_PIVOT2 (ID,strcardid,SwipeDate,PUNCHTIME)
VALUES (4,'3716817970','01/08/2013','08:52:31')
INSERT INTO ALL_PUNCHES_PIVOT2 (ID,strcardid,SwipeDate,PUNCHTIME)
VALUES (5,'3716817970','01/08/2013','17:50:14')
INSERT INTO ALL_PUNCHES_PIVOT2 (ID,strcardid,SwipeDate,PUNCHTIME)
VALUES (6,'3716817970','01/08/2013','17:50:17')

SELECT * FROM ALL_PUNCHES_PIVOT2;

ID  strcardid   SwipeDate   PunchTime
1   3716817970  01/08/2013  08:47:53
2   3716817970  01/08/2013  08:47:56
3   3716817970  01/08/2013  08:52:29
4   3716817970  01/08/2013  08:52:31
5   3716817970  01/08/2013  17:50:14
6   3716817970  01/08/2013  17:50:17

Now I am using the below Pivot to get the result

SELECT * FROM
    (
        SELECT strcardid,SwipeDate,PunchTime FROM ALL_PUNCHES_PIVOT2
    ) P 
            PIVOT (
            min([PunchTime]) FOR SwipeDate in ([01/08/2013])    
              ) as Pvt

Below is the result I am getting

strcardid   01/08/2013
3716817970  08:47:53

how ever I want the output as below

strcardid   Swipedate  time1     time2    time3    time4    time5    time6
3716817970  01/08/2013 08:47:53  08:47:56 08:52:29 08:52:31 17:50:14 17:50:17

Please help me on this

Answer 1

You can use ROW_NUMBER() to get the numbers for times and then do the pivot

select * from 
(
    select strcardid, SwipeDate, PunchTime, ROW_NUMBER() OVER (PARTITION BY strcardid, SwipeDate ORDER BY PunchTime) rowno
    from ALL_PUNCHES_PIVOT2
) p
PIVOT (min(PunchTime) FOR rowno in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15])) as pvt

Answer 2

Any good?

;WITH CTE as(
    SELECT  *, ROW_NUMBER( ) OVER (PARTITION BY strcardid ORDER BY SwipeDate ASC) AS RowNum
    FROM    ALL_PUNCHES_PIVOT2
) 
SELECT  cte.strcardid
    ,   cte.SwipeDate
    ,   MAX(CASE cte.RowNum WHEN 1 THEN cte.PUNCHTIME ELSE '' END) as time1
    ,   MAX(CASE cte.RowNum WHEN 2 THEN cte.PUNCHTIME ELSE '' END) as time2
    ,   MAX(CASE cte.RowNum WHEN 3 THEN cte.PUNCHTIME ELSE '' END) as time3
    ,   MAX(CASE cte.RowNum WHEN 4 THEN cte.PUNCHTIME ELSE '' END) as time4
    ,   MAX(CASE cte.RowNum WHEN 5 THEN cte.PUNCHTIME ELSE '' END) as time5
    ,   MAX(CASE cte.RowNum WHEN 6 THEN cte.PUNCHTIME ELSE '' END) as time6
    ,   MAX(CASE cte.RowNum WHEN 7 THEN cte.PUNCHTIME ELSE '' END) as time7
    ,   MAX(CASE cte.RowNum WHEN 8 THEN cte.PUNCHTIME ELSE '' END) as time8
    ,   MAX(CASE cte.RowNum WHEN 9 THEN cte.PUNCHTIME ELSE '' END) as time9
    ,   MAX(CASE cte.RowNum WHEN 10 THEN cte.PUNCHTIME ELSE '' END) as time10
    ,   MAX(CASE cte.RowNum WHEN 11 THEN cte.PUNCHTIME ELSE '' END) as time11
    ,   MAX(CASE cte.RowNum WHEN 12 THEN cte.PUNCHTIME ELSE '' END) as time12
    ,   MAX(CASE cte.RowNum WHEN 13 THEN cte.PUNCHTIME ELSE '' END) as time13
    ,   MAX(CASE cte.RowNum WHEN 14 THEN cte.PUNCHTIME ELSE '' END) as time14
    ,   MAX(CASE cte.RowNum WHEN 15 THEN cte.PUNCHTIME ELSE '' END) as time15
FROM    CTE
GROUP BY cte.strcardid, cte.SwipeDate

Results:

strcardid   SwipeDate   time1   time2   time3   time4   time5   time6   time7   time8   time9   time10  time11  time12  time13  time14  time15
3716817970  01/08/2013  08:47:53    08:47:56    08:52:29    08:52:31    17:50:14    17:50:17

SQL Fiddle

Edit: Szymon has the better answer, but I might leave this here as an example of an alternative pivot.