Is there a way to dynamically create SQL columns in a selected query?

Question

My current SQL query takes the last 6 months and selects sales, for example

  SUM(CASE WHEN ci.InvoiceDate >= '20160501' AND ci.InvoiceDate <= '20160531' THEN cid.QuantityOrdered ELSE 0 END) AS 'May',
  SUM(CASE WHEN ci.InvoiceDate >= '20160601' AND ci.InvoiceDate <= '20160630' THEN cid.QuantityOrdered ELSE 0 END) AS 'June',
  SUM(CASE WHEN ci.InvoiceDate >= '20160701' AND ci.InvoiceDate <= '20160731' THEN cid.QuantityOrdered ELSE 0 END) AS 'July',
  SUM(CASE WHEN ci.InvoiceDate >= '20160801' AND ci.InvoiceDate <= '20160831' THEN cid.QuantityOrdered ELSE 0 END) AS 'August',
  SUM(CASE WHEN ci.InvoiceDate >= '20160901' AND ci.InvoiceDate <= '20160930' THEN cid.QuantityOrdered ELSE 0 END) AS 'September',
  SUM(CASE WHEN ci.InvoiceDate >= '20161001' AND ci.InvoiceDate <= '20161030' THEN cid.QuantityOrdered ELSE 0 END) AS 'October',

However, when I launch this report in December because I am heavily encoded in the names Month, I will not receive sales figures in November.

Ideally, I would like some form of code to dynamically create a selection request with the last 6 months in it.

Is it possible? Is there any documentation someone can point me to learn more about dynamic SQL?

Thanks everyone!

Answer 1

Perhaps this will help you get started.

Declare @Date Date  = '2016-10-01'
Declare @Months int = 6

Declare @SQL varchar(max)=''
;with cte0(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N))
    , cteD(D) As (Select DateAdd(MM,1-Row_Number() over (Order By (Select NULL)),@Date) From cte0 N1, cte0 N2) 
    , cteDate as (Select Top (@Months) DateR1=D,DateR2=DateAdd(DD,-1,DateAdd(MM,1,D)),Title=DateName(MM,D) From cteD)
Select @SQL=@SQL+','+QuoteName(Title)+'=sum(case when ci.InvoiceDate Between '''+CONVERT(char(10), DateR1,126) +''' and '''+CONVERT(char(10), DateR2,126)+''' Then cid.QuantityOrdered Else 0 End))'+char(13) 
 From  (Select Top (@Months) * From cteDate Order by DateR1) A

Select @SQL = 'Select '+Stuff(@SQL,1,1,'')+' From YourTable '
Exec(@SQL)

Creates the following SQL

Select [May]=sum(case when ci.InvoiceDate Between '2016-05-01' and '2016-05-31' Then cid.QuantityOrdered Else 0 End))
,[June]=sum(case when ci.InvoiceDate Between '2016-06-01' and '2016-06-30' Then cid.QuantityOrdered Else 0 End))
,[July]=sum(case when ci.InvoiceDate Between '2016-07-01' and '2016-07-31' Then cid.QuantityOrdered Else 0 End))
,[August]=sum(case when ci.InvoiceDate Between '2016-08-01' and '2016-08-31' Then cid.QuantityOrdered Else 0 End))
,[September]=sum(case when ci.InvoiceDate Between '2016-09-01' and '2016-09-30' Then cid.QuantityOrdered Else 0 End))
,[October]=sum(case when ci.InvoiceDate Between '2016-10-01' and '2016-10-31' Then cid.QuantityOrdered Else 0 End))
 From YourTable 

Answer 2

6 :

declare @months nvarchar(max) = ''

;with cte as
(select DATENAME(month, dateadd(MONTH, 0, getdate())) mn, 0 v
union all
select DATENAME(month, dateadd(MONTH, v - 1, getdate())) mn, v - 1
from cte
where v > -5
)
select @months = @months + '[' + mn + '], '
from cte

set @months = SUBSTRING(@months, 1, LEN(@months) - 1)

declare @q nvarchar(max)

set @q = '
select *
from
(
select QuantityOrdered qo
, DATENAME(month, InvoiceDate) mn
from invoicetable
) as s
pivot
(sum(qo) for mn in (' + @months + ')
) p'

exec(@q)