以日期為中心並按小時計算值

我希望這是有道理的。我有一個記錄插入行的日期和時間的數據庫。我有一個查詢，它將選擇併計算每小時的插入次數。我想對此進行旋轉，以便將日期顯示為行，將小時顯示為列。

例如：

 DateTime      Hour1     Hour2    Hour3    |
+-----------------------------------------+
| 2016-01-01     11        10      35      |
| 2016-01-02     12        15      25      |

我目前的查詢顯示為：

 DateTime      Hour       total    |
+-----------------------------------+
| 2016-01-01     1          11      |
| 2016-01-01     2          10     

詢問 ：

SELECT CAST([datetime_received] as date) AS ForDate,
      DATEPART(hour,[datetime_received]) AS OnHour,
      COUNT(*) AS Totals
FROM [claims]
where [datetime_received] between '2018-08-03 00:00:00.000' and '2018-08-03 23:59:00.000'
GROUP BY CAST([datetime_received] as date),
      DATEPART(hour,[datetime_received])

我對樞軸查詢沒有太多經驗，因此將不勝感激。

謝謝

好的，所以經過幾次試錯查詢後，我設法解決了這個問題。

這可能不是最好的方法，但它似乎工作正常。

select *
from 
(
 select CAST([datetime_received] as date) AS ForDate,   DATEPART(hour,[datetime_received]) AS OnHour,     COUNT(*) AS Totals
 from [claims]
 where [datetime_received] between '2019-07-01 00:00:00.000' and '2019-07-31 23:59:00.000'
 GROUP BY CAST([datetime_received] as date),
      DATEPART(hour,[datetime_received])


) src
pivot
(
 sum(Totals) 
 for OnHour in    ([0],[1], [2], [3],[4], [5], [6],[7],[8], [9], [10],[11], [12], [13],[14], [15], [16],[17],[18], [19],[20],[21], [22], [23]) 
) piv 
order by ForDate

如果有人可以幫助我完善這一點，我將不勝感激。

謝謝

引用自：https://dba.stackexchange.com/questions/244322