How to get both month and year together from a date in sql server 2005?

See more:

SQL-Server-2005

SQL-Server

Hi All,

For the below query (sdate is column name and table name is storedata)

WITH TotalMonths AS (SELECT T1.[Month], T2.[Year]
 FROM ((SELECT DISTINCT Number AS [Month]
FROM MASTER.dbo.spt_values WHERE [Type] = 'p' AND Number BETWEEN 1 AND 12) T1 CROSS JOIN
 (SELECT DISTINCT DATEPART(year, sdate) AS [Year]
FROM storedata) T2))

SELECT CTE.[Year], CTE.[Month], ISNULL(T3.[Sum], 0) areasum
FROM TotalMonths CTE LEFT OUTER JOIN (
SELECT SUM(areasft) [Sum], DATEPART(YEAR, sdate) [Year], DATEPART(MONTH, sdate) [Month]
FROM storedata
GROUP BY DATEPART(YEAR, sdate) ,DATEPART(MONTH, sdate)) T3
ON CTE.[Year] = T3.[Year] AND CTE.[Month] = T3.[Month] WHERE CTE.[Year]>'2007'
ORDER BY CTE.[Year], CTE.[Month]

I am getting result set like below:

YEAR MONTH AREASUM
2008	1	0
2008	2	1193
2008	3	4230
2008	4	350
2008	5	2200
2008	6	4660
2008	7	0
2008	8	6685
2008	9	0
2008	10	3051
2008	11	7795
2008	12	2940
2009	1	1650
2009	2	3235
2009	3	2850
2009	4	6894
2009	5	3800
2009	6	2250
2009	7	1000
2009	8	1800
2009	9	1550
2009	10	2350
2009	11	0
2009	12	1800

But I have to combine both month and year in single column. The reult set should like below.

JAN/08 O
FEB/08 1193
.. ..
.. ..
DEC/O9 1800

How can I modify my query? (I should display for all the years and months even if there is no area for a month)

Regards,
N.SRIRAM