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

Question

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?

Answer 1

Try building a reference table of months, and JOINing on it. It's the quickest way to do this with months in varchar datatype.

declare @foo table (id int, [mon] varchar(100), cost int)    
declare @mon table (mon varchar(100), orderWeight int)

INSERT INTO @mon (mon, orderWeight)
   VALUES ('Jan',1), ('Feb',2),('Mar',3),('Apr',4),('May',5),('Jun',6),('Jul',7),
          ('Aug',8),('Sep',9),('Oct',10),('Nov',11),('Dec',12)

INSERT INTO @foo(id, [mon], cost)
VALUES ( 1    ,'Jan' ,     200),
( 1    ,'Mar',      204),
( 1    ,'May' ,    200),
( 1    ,'Dec'  ,    201)

select f.id,
       m.[mon] , 
       f.cost
from @mon as m 
left join @foo as f on m.mon = f.mon
order by m.orderWeight

Results:

Your ordering will now be guaranteed with the order by orderWeight.