Is there a way to dynamically produce SQL Columns in a select query?

Question

My Current sql query takes the last 6 months and selects the sales, like so

  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 run this report in December, because I have hard coded in the Month names, I won't get the November sales figures.

Ideally I would like some form of code that dynamically produces select query with the last 6 months in it.

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

Thanks everyone!

You want a [dynamic pivot](http://stackoverflow.com/questions/10404348), which is possible in SQL Server but can be messy. If you can do this in the client tool (report, form, web page, etc.) the UI tools for dynamic pivots are usually MUCH cleaner and easier. — D Stanley, Nov 10 '16 at 23:28

John Cappelletti · Answer 1 · 2016-11-11T00:38:19.697

Perhaps this will get you 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)

Generates 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

`EOMONTH` and `Format` not recognised as functions? – Brendan Gooden Nov 11 '16 at 00:15 — Brendan Gooden, Nov 11 '16 at 00:15
@BrendanGooden Updated for 2008 – John Cappelletti Nov 11 '16 at 00:38 — John Cappelletti, Nov 11 '16 at 00:38
@BrendanGooden Removed Format() as well – John Cappelletti Nov 11 '16 at 00:42 — John Cappelletti, Nov 11 '16 at 00:42

artm · Answer 2 · 2016-11-10T23:59:38.443

Get last 6 months names and build the dynamic query using a pivot:

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)

Is there a way to dynamically produce SQL Columns in a select query?

2 Answers2