行转列求助!!

[telankes2000]

每人每天都有很多次打卡记录 有上班卡和下班卡
但是由于记录太多 所以报表只显示最新的10笔数据
要实现这样的格式
User IN IN IN OUT OUT OUT IN IN OUT OUT
张三 07:35 07:53 07:59 10:30 11:11 12:10 13:20 13:25 17:35 18:30
这是员工可能的打卡情况 实际上每天只需打4次卡 上午上班 ，上午下班 ，中午上班 ，下午下班
原始数据的格式如下： 0表示上班卡 1表示下班卡
User Time INOUT
张三 2015-03-01 07:35:01 0
张三 2015-03-01 07:53:01 0
张三 2015-03-01 07:59:01 0
张三 2015-03-01 10:30:01 1
张三 2015-03-01 11:11:01 1
张三 2015-03-01 12:10:01 1
张三 2015-03-01 17:35:01 1
张三 2015-03-01 18:30:01 1
实际打卡数量可能大于10笔

[Tiger_Zhao]

WITH table1([User],Time,INOUT) AS (
	SELECT '张三',Convert(datetime,'2015-03-01 07:35:01',120),0 UNION ALL
	SELECT '张三','2015-03-01 07:53:01',0 UNION ALL
	SELECT '张三','2015-03-01 07:59:01',0 UNION ALL
	SELECT '张三','2015-03-01 10:30:01',1 UNION ALL
	SELECT '张三','2015-03-01 11:11:01',1 UNION ALL
	SELECT '张三','2015-03-01 12:10:01',1 UNION ALL
	SELECT '张三','2015-03-01 17:35:01',1 UNION ALL
	SELECT '张三','2015-03-01 18:30:01',1 
)
,a1 AS (
    SELECT [user],
           Convert(varchar(5),
                   CASE INOUT WHEN 0 THEN 'IN' ELSE 'OUT' END
                  ) INOUT,
           ROW_NUMBER()OVER(PARTITION BY User ORDER BY Time DESC) rn
      FROM table1
)
,a2 AS (
    SELECT 1 no, *
      FROM a1
     PIVOT (
            MAX(INOUT)
            FOR rn IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
           ) P
)
,b1 AS (
    SELECT [user],
           Convert(varchar(5),time,8) time,
           ROW_NUMBER()OVER(PARTITION BY User ORDER BY Time DESC) rn
      FROM table1
)
,b2 AS (
    SELECT 2 no, *
      FROM b1
     PIVOT (
            MAX(time)
            FOR rn IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
           ) P
)
    SELECT *
      FROM (
            SELECT * FROM a2
            UNION ALL
            SELECT * FROM b2
           ) t
  ORDER BY [user], no

         no user 1     2     3     4     5     6     7     8     9     10
----------- ---- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
          1 张三 OUT   OUT   OUT   OUT   OUT   IN    IN    IN    NULL  NULL
          2 张三 18:30 17:35 12:10 11:11 10:30 07:59 07:53 07:35 NULL  NULL