sql-server - 如何在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

现在我正在使用下面的 Pivot 来获取结果

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

下面是我得到的结果

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

我怎么想要如下的输出

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

请帮我解决这个问题

Answer 1

您可以使用ROW_NUMBER()获取次数的数字，然后进行数据透视

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