sql server - need the sql query format for previous 11 months data -

i running report pass paramater 'jan-13' or 'may-13' or 'jun-12' may thing.

select cardname,cardcode ,dln1.quantity  odln inner join dln1 on odln.docentry = odln.docentry  odln.docdate = 'jan-13' (this doc date datetime format) in report chose jan-13  

i need output past 11 months of quantity current month jan-13

cardname    cardcode  jan-13 dec-12 nov-12 oct-12 sep-12  aug-12  jul-12   jun-12  may-12   apr -12  mar-12 feb-12    efiglobal     a2       12     10      12      10    8       12      3       2        12       11       4      6 

this work sqlserver-2008+

create test table first

create table test(cardname varchar(20),cardcode int, quantity int, docdate datetime) insert test values('ab', 1, 2, '2013-01-01 20:30') insert test values('ab', 1, 2, '2012-05-01 20:30') insert test values('abc', 3, 4, '2012-03-01 20:30') go 

this script:

-- @a input declare @a char(6) = 'jan-13' declare @to datetime = dateadd(mm, 1, convert(datetime, '01-'+@a, 16)) declare @from datetime = dateadd(yy, -1, @to) declare @col varchar(200)  ;with dates ( select @to-1 m union select dateadd(mm, -1, m) dates m > dateadd(mm, 1, @from) ) select @col = coalesce(@col + ',', '') + '['+right(stuff(convert(varchar(9), m, 6), 7, 1, '-'), 6)+']' dates  declare @sql nvarchar(max) = ' ;with x ( select cardname,cardcode ,quantity, right(stuff(convert(varchar(9), docdate, 6), 7, 1, ''-''), 6) dd  /* -- use in case remove comments markers (/* , */) ( select cardname,cardcode ,dln1.quantity  odln inner join dln1 on odln.docentry = odln.docentry  ) */ test docdate >= @from , docdate < @to ) select * x pivot (sum(quantity) [dd] in ('+@col+')) pvt '  exec sp_executesql @sql, n'@from datetime, @to datetime', @from, @to 

result:

cardname cardcode   jan-13 dec-12 nov-12 oct-12 sep-12 aug-12 jul-12 jun-12 may-12 apr-12 mar-12 feb-12 ab       1          2      null   null   null   null   null   null   null   2      null   null   null abc      3          null   null   null   null   null   null   null   null   null   null   4      null