How to get month and year in single column and grouping the data for all the years and months?

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

Collapse
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)

Regard开发者_StackOverflow社区s,

N.SRIRAM

Try:

SELECT CONVERT(VARCHAR(3), DATENAME(MONTH, CTE.Month), 7) + '/' + RIGHT(CTE.Year, 2)

instead of using your first 2 columns from your SELECT.

You seem to be saying that you're getting the right data from your original query, but the wrong format. So

1. Make a view out of the query you originally posted.
2. Build a SELECT query based on that view to give you the format you want.

Let's say you do this:

CREATE VIEW wibble AS <your original query goes here>

Then you can just query wibble to correct the formatting.

select 
case
    when month = 1 then 'Jan/'
    when month = 2 then 'Feb/'
    when month = 3 then 'Mar/'
    when month = 4 then 'Apr/'
    when month = 5 then 'May/'
    when month = 6 then 'Jun/'
    when month = 7 then 'Jul/'
    when month = 8 then 'Aug/'
    when month = 9 then 'Sep/'
    when month = 10 then 'Oct/'
    when month = 11 then 'Nov/'
    when month = 12 then 'Dec/'
    else 'Err'
end || substring(cast(year as CHAR(4)), 3, 2) as yearmonth,
areasum from wibble;