sql server 的语句统计外包人员出勤时间

需要sql语句将这张表
name 姓名 dept部门组织 date 考勤日期 timeTotal 工时总计
这四个栏位的数据组成如下图的查询结果。
需求：
每个月1-31天所有的数据对应栏位显示，最上面那个2019-04的抬头和每个人的最后total工时总计可以不用计算。只要帮我拼出sql语句就好了，应该是个交叉报表，可能高手还要指导我一下怎么虚拟出一个月31个日的列名，请高手提供完整的思路最好是代码，感谢

可以用临时表的存储过程来做，也可以新增一张实体表，抬头是月的每日固定列，请高手多多指点

SELECT
    t.dept,
    t.name,
    MAX ( CASE WHEN RIGHT(t.[date], 2) = '01' THEN t.timeTotal END ) '1',
    MAX ( CASE WHEN RIGHT(t.[date], 2) = '02' THEN t.timeTotal END ) '2',
    MAX ( CASE WHEN RIGHT(t.[date], 2) = '03' THEN t.timeTotal END ) '3' 
FROM
    Test t 
GROUP BY
    t.dept, t.name

RIGHT(t.[date], 2) // 从右往左截取2个字符串

SELECT 
     dept,
     name,
     SUM(CASE WHEN DAY(date)=1 THEN timeTotal ELSE 0 END) 一号,
     SUM(CASE WHEN DAY(date)=2 THEN timeTotal ELSE 0 END) 二号,
     SUM(CASE WHEN DAY(date)=3 THEN timeTotal ELSE 0 END) 三号,
     SUM(CASE WHEN DAY(date)=4 THEN timeTotal ELSE 0 END) 四号,
     SUM(CASE WHEN DAY(date)=5 THEN timeTotal ELSE 0 END) 五号,
     SUM(CASE WHEN DAY(date)=6 THEN timeTotal ELSE 0 END) 六号,
     SUM(CASE WHEN DAY(date)=7 THEN timeTotal ELSE 0 END) 七号,
     SUM(CASE WHEN DAY(date)=8 THEN timeTotal ELSE 0 END) 八号,
     SUM(CASE WHEN DAY(date)=9 THEN timeTotal ELSE 0 END) 九号,
     SUM(CASE WHEN DAY(date)=10 THEN timeTotal ELSE 0 END) 十号,

     SUM(CASE WHEN DAY(date)=11 THEN timeTotal ELSE 0 END) 十一号,
     SUM(CASE WHEN DAY(date)=12 THEN timeTotal ELSE 0 END) 十二号,
     SUM(CASE WHEN DAY(date)=13 THEN timeTotal ELSE 0 END) 十三号,
     SUM(CASE WHEN DAY(date)=14 THEN timeTotal ELSE 0 END) 十四号,
     SUM(CASE WHEN DAY(date)=15 THEN timeTotal ELSE 0 END) 十五号,
     SUM(CASE WHEN DAY(date)=16 THEN timeTotal ELSE 0 END) 十六号,
     SUM(CASE WHEN DAY(date)=17 THEN timeTotal ELSE 0 END) 十七号,
     SUM(CASE WHEN DAY(date)=18 THEN timeTotal ELSE 0 END) 十八号,
     SUM(CASE WHEN DAY(date)=19 THEN timeTotal ELSE 0 END) 十九号,
     SUM(CASE WHEN DAY(date)=20 THEN timeTotal ELSE 0 END) 二十号,

     SUM(CASE WHEN DAY(date)=21 THEN timeTotal ELSE 0 END) 二一号,
     SUM(CASE WHEN DAY(date)=22 THEN timeTotal ELSE 0 END) 二二号,
     SUM(CASE WHEN DAY(date)=23 THEN timeTotal ELSE 0 END) 二三号,
     SUM(CASE WHEN DAY(date)=24 THEN timeTotal ELSE 0 END) 二四号,
     SUM(CASE WHEN DAY(date)=25 THEN timeTotal ELSE 0 END) 二五号,
     SUM(CASE WHEN DAY(date)=26 THEN timeTotal ELSE 0 END) 二六号,
     SUM(CASE WHEN DAY(date)=27 THEN timeTotal ELSE 0 END) 二七号,
     SUM(CASE WHEN DAY(date)=28 THEN timeTotal ELSE 0 END) 二八号,
     SUM(CASE WHEN DAY(date)=29 THEN timeTotal ELSE 0 END) 二九号,
     SUM(CASE WHEN DAY(date)=30 THEN timeTotal ELSE 0 END) 三十号,
     SUM(CASE WHEN DAY(date)=31 THEN timeTotal ELSE 0 END) 三十一号,
     SUM(timeTotal) total
FROM table_name
GROUP BY dept,name,LEFT(date,7)