統計表 AA 中 AA_YWLX(業務類型)在每年每月的出現次數。
SELECT
YEAR(AA_YWRQ) AS [Year],
MONTH(AA_YWRQ) AS [Month],
AA_YWLX AS BizType,
COUNT(*) AS Cnt
FROM AA
GROUP BY YEAR(AA_YWRQ), MONTH(AA_YWRQ), AA_YWLX
ORDER BY [Year], [Month], BizType
| AA_YWRQ | AA_YWLX |
|---|---|
| 2014-03-10 | TypeA |
| 2014-03-15 | TypeA |
| 2014-03-20 | TypeB |
| 2014-04-01 | TypeA |
| Year | Month | BizType | Cnt |
|---|---|---|---|
| 2014 | 3 | TypeA | 2 |
| 2014 | 3 | TypeB | 1 |
| 2014 | 4 | TypeA | 1 |
SELECT [Year], [Month], [TypeA], [TypeB], [TypeC]
FROM (
SELECT YEAR(AA_YWRQ) AS [Year], MONTH(AA_YWRQ) AS [Month], AA_YWLX
FROM AA
) src
PIVOT (COUNT(AA_YWLX) FOR AA_YWLX IN ([TypeA], [TypeB], [TypeC])) pvt
ORDER BY [Year], [Month]
得到橫向報表,便於前端直接綁定圖表。