sql server pivot table example cross table using to display yearly report

example
BEGIN
DECLARE @YEAR INT;
SET @YEAR = 2014;
WITH BaseQuery AS(
  Select R.OVR_REQUEST_ID AS RID, Month(R.CREATE_DATE) as REQUEST_Month,
   Year(R.CREATE_DATE) as [Year] ,
  C.CATEGORY_NAME
   from OVR_REQUEST R
   inner join OVR_CATEGORIES C on R.CATEGORY_ID =C.CATEGORY_ID
                           
)
                           
SELECT CATEGORY_NAME , [1] AS [January],[2] AS [February],
                           
  [3] AS [March],[4] AS [April],[5] AS [May],[6] AS [June],
                           
  [7] AS [July], [8] AS [August],[9] AS [September],
                           
  [10] AS [October], [11] AS [November],[12] AS [December]
                           
FROM BaseQuery
                           
PIVOT(SUM(RID) FOR REQUEST_Month IN ([1],[2],[3],[4],[5],
                           
  [6],[7],[8],[9],[10],[11],[12])) AS PVT
  WHERE YEAR=@YEAR
                           
ORDER BY January DESC
END



output

Another Example

Select CATEGORY_NAME ,[Year] ,[Month],  sum(OVR_REQUEST_ID) REQUES_TOTAL from
(
   Select R.OVR_REQUEST_ID, Month(R.CREATE_DATE) as [Month], Year(R.CREATE_DATE) as [Year] ,C.CATEGORY_NAME
   from OVR_REQUEST R
   inner join OVR_CATEGORIES C on R.CATEGORY_ID =C.CATEGORY_ID
   --where date >= '2-1-2014' and date <= '2-28-2014'
) as a
Group by   CATEGORY_NAME ,[Year],[Month]