小菜鸟也有大梦想 2017-03-16 02:24 采纳率: 50%
浏览 2800
已采纳

SQL 日期行转列问题~~~~~

图片说明这个怎么转啊,可以写sql代码吗,谢谢大家

  • 写回答

2条回答 默认 最新

  • Tiger_Zhao 2017-03-16 03:29
    关注
    SET DATEFIRST 1 -- 每周从星期一开始
    
    ;WITH
    /* 测试数据 */
    table1(id,name,checktime) AS (
        SELECT 10006,'a','2017-03-07' UNION ALL
        SELECT 10007,'b','2017-03-13' UNION ALL
        SELECT 10007,'b','2017-03-14' UNION ALL
        SELECT 10007,'b','2017-03-15' UNION ALL
        SELECT 10010,'c','2017-03-07' UNION ALL
        SELECT 10010,'c','2017-03-13' UNION ALL
        SELECT 10018,'d','2017-03-09 8:13' UNION ALL
        SELECT 10018,'d','2017-03-09 8:14' UNION ALL
        SELECT 10018,'d','2017-03-09 8:29'
    ),
    a(id,name,checkday,[week],[weekday]) AS (
        SELECT DISTINCT
               id,
               name,
               CONVERT(varchar(10),checktime,120),
               DATEPART(week,checktime),
               DATEPART(weekday,checktime)
          FROM table1
    )
    SELECT id,
           name,
           [week],
           [1],[2],[3],[4],[5],[6],[7]
      FROM a
     PIVOT (
            MAX(checkday)
            FOR [weekday] IN ([1],[2],[3],[4],[5],[6],[7])
           ) p
    
             id name        week 1          2          3          4          5          6          7
    ----------- ---- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
          10006 a             11 NULL       2017-03-07 NULL       NULL       NULL       NULL       NULL
          10007 b             12 2017-03-13 2017-03-14 2017-03-15 NULL       NULL       NULL       NULL
          10010 c             11 NULL       2017-03-07 NULL       NULL       NULL       NULL       NULL
          10010 c             12 2017-03-13 NULL       NULL       NULL       NULL       NULL       NULL
          10018 d             11 NULL       NULL       NULL       2017-03-09 NULL       NULL       NULL
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?