sql - SQL Pivot 行和列

Question

用我的小桌子

skill   dept     startdate     starttime
good    A        2020-09-01    08:00:00.0000000
good    A        2020-09-03    08:50:00.0000000
bad     A        2020-09-04    18:00:00.0000000
bad     B        2020-09-05    09:53:00.0000000
avg     B        2020-09-06    13:00:05.0000000
avg     B        2020-09-07    13:50:05.0000000

我的查询

SELECT * FROM 
(
    SELECT 
        skill, MIN(starttime) AS MinTime,Max(starttime) AS MaxTime,  startdate 
    FROM 
        table2
    group by starttime, startdate, skill
 )t       
PIVOT(
    COUNT(skill) 
    FOR skill IN ([Mon], [Tue], [Wed], [Thu], [Fri], [Sat],  [Sun])
)
AS pivot_table;

结果

MinTime             MaxTime             startdate   Mon Tue Wed Thu Fri Sat Sun
08:00:00.0000000    08:00:00.0000000    2020-09-01  0   0   0   0   0   0   0
08:50:00.0000000    08:50:00.0000000    2020-09-03  0   0   0   0   0   0   0
09:53:00.0000000    09:53:00.0000000    2020-09-05  0   0   0   0   0   0   0
13:00:05.0000000    13:00:05.0000000    2020-09-06  0   0   0   0   0   0   0
13:50:05.0000000    13:50:05.0000000    2020-09-07  0   0   0   0   0   0   0
18:00:00.0000000    18:00:00.0000000    2020-09-04  0   0   0   0   0   0   0

不确定为什么不按技能领域计算总组数？
希望有人能指出正确的方向。谢谢你。

标签： sqlsql-server

Answer 1

试试这个：

  SELECT * FROM 
  (
      SELECT 
          skill, MIN(starttime) AS MinTime,Max(starttime) AS MaxTime,  DATENAME(WEEKDAY, startdate) as startdate
      FROM 
          table_2
      group by starttime, startdate, skill
   ) t
  PIVOT(
      COUNT(Skill) 
      FOR startdate IN (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday,  Sunday)
  )
  AS pivot_table;