Dynamic SQL?

Other SQL Server Topics
1

Hello:

I have a query that creates several columns 12 of which are for each month of the year. I cannot simply name them January, February, etc.....This would work fine if I was always looking for data for the calendar year (Jan - Dec). I am not though, it is always 12 months but could be February of 2015 through January of 2016. These dates are based on the input of the user.

Here is an example of the piece of the code I am referencing above:

isnull ( sum ( case when month(mm.ReportingMonth) = 01 then mm.membermonths end ),0) as 'January'

I do not want to name this 'January' though but would like it to be named dynamically based on user input. Something like month(mm.reportingmonth)+year(mm.reportingmonth).

I don't know how to do that though, can anyone please help me?

Thank you,

John

2

Would the DATENAME function help? thus DATENAME(month, mm.ReportingMonth)

3

Can you use that function as part of a case statement?

4

You can use a function in a case expression, but you cannot use a function as the alias in the select list. So in your example, you couldn't write a query like this:

isnull ( sum ( case when month(mm.ReportingMonth) = 01 then mm.membermonths end ),0) as DATENAME(month, mm.ReportingMonth)

You would have to construct a dynamic SQL string and then use EXEC or sp_executesql to execute that query string. For a variety of reasons, that is not a recommended practice in most cases.

What you could do is use PIVOT like in the example shown below. You can copy and paste this code to an SSMS window and run it to see what it does and then adapt to your situation.

CREATE TABLE #tmp (reportingmonth DATE, membermonths INT);
INSERT INTO #tmp VALUES
('20150101',200),('20150121', 22),
('20150207',100),('20150222', 1);	

SELECT * FROM 
(
	SELECT
		DATENAME(MONTH,reportingmonth) AS [Month],
		SUM(membermonths) AS membermonthsTotal
	FROM
		#tmp
	GROUP BY
		DATENAME(MONTH,reportingmonth)
)s
PIVOT
(MAX(membermonthsTotal) FOR [Month] IN ([January],[February])) P

DROP TABLE #tmp;