sql - SQL Server 2008 中的分桶数据

Question

我从这样的数据开始:

+----------+------------------+--------+
| specimen |       date       | bucket |
+----------+------------------+--------+
| 31598D   | 3/3/2010 11:38   |     10 |
| A113899  | 2/10/2010 13:50  |     11 |
| A121375  | 12/17/2010 10:06 |      2 |
| A122115  | 6/14/2010 9:33   |     10 |
| A122119  | 5/19/2010 10:08  |      3 |
| A122124  | 6/30/2010 11:43  |      4 |
| DD58834  | 6/17/2010 10:08  |      1 |
| 31598A   | 3/3/2010 11:36   |     10 |
+----------+------------------+--------+

我想知道是否可以将其转换为这样的频率分布:

我会在 SQL Server 中使用 pivot 函数吗？如果是，怎么办？

请注意，我确实可以访问 SSRS，并且可以将其用作应对这一挑战的资源。

非常感谢您的指导和时间

Answer 1

WITH    bm (maxbucket) AS
        (
        SELECT  MAX(bucket)
        FROM    mydata
        ),
        buckets (bucket) AS
        (
        SELECT  1
        UNION ALL
        SELECT  bucket + 1
        FROM    buckets
        JOIN    bm
        ON      bucket < maxbucket
        )
SELECT  bucket, p.*
FROM    (
        SELECT  b.bucket, DATEPART(month, dt) AS mon
        FROM    buckets b
        LEFT JOIN
                mydata m
        ON      m.bucket = b.bucket
        ) q
PIVOT   (
        COUNT(mon)
        FOR
        mon IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
        ) p