[SOLVED] How to fill all the months of the year in order using TSQL

How to fill all the months of the year in order using TSQL

I have the following table:

id   month   cost
------------------
1    Jan      200
1    Mar      204
1    May      200
1    Dec      201

I need an output like (order by month including the other months of a year-displaying all 12 months):

to   month   cost
------------------
1    Jan      200
NULL Feb      NULL
1    Mar      204
....
....
....
1    Dec      201

any idea or solution how to do this in TSQL?

edit:: month is extracted from a datetime value. in real world I'll have to show previous 12 months from last month in a DESC order! Any suggestion for that?

Solution

How about this? The result contains month and year, but you can strip it as you want.

;with months
as
(
select dateadd(month, -1, dateadd(day, datediff(day, 0, getdate()), 0)) as m
union all
select dateadd(month, -1, m)
from months
where   m > dateadd(month, -12, getdate())
)

-- Testdata
,yourTable(id,somedate,cost)
as
(
    select 1, '2011-01-03', 200
    union all
    select 1, '2011-03-06', 204
    union all
    select 1, '2010-05-09', 200
    union all
    select 1, '2010-05-19', 201
    union all
    select 1, '2010-12-02', 201
)
-- end testdata

select  yt.id
        ,datename(month,coalesce(yt.somedate, m.m)) as [month]
        ,datename(year,coalesce(yt.somedate, m.m)) as [year]
        --,yt.cost
        ,sum(yt.cost) as cost
from    months m
left join yourTable yt
    on  datepart(year, yt.someDate) = DATEPART(year, m.m)
    and datepart(month, yt.someDate) = DATEPART(month, m.m)
group by
    yt.id
    ,datename(month,coalesce(yt.somedate, m.m))
    ,datename(year,coalesce(yt.somedate, m.m))
    ,m.m
order by m.m desc

Edit: Altered solution to support sum. Remove the group by-section and alter the comment of cost, to get the old solution.