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

Question

这个怎么转啊，可以写sql代码吗，谢谢大家

Answer 1

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