How do I get 12 months as column names in SQL

Question

0.00/5 (No votes)

See more:

I am passing Month and Year as Parameters to the Query.
I need Last twelve trailing months based on month and year.

For Eg:
I am giving January , 2016 as parameter.
I want to get the column names as

Jan-15,Feb-15,Mar-15,Apr-15,,,,,,,,,,,Dec-15

Posted 14-Jan-16 1:26am

saju A Salim

Updated 14-Jan-16 1:56am

Leo Chapiro

v3

Add a Solution

Comments

ZurdoDev 14-Jan-16 7:31am

You'll need to probably dump last twelve months into a temp table using the date passed in and then pivot it and format the columns appropriately.

Where are you stuck?

2 solutions

Solution 2

As said in solution 1 this can be done in different ways. However, if possible I would avoid using dynamic column names. Very often using dynamic names introduces other problems and complexity when the result set is used. Depending on the situation the order of the columns may change or the calling side is unaware of the formatted names and so on.

So if possible, I'd provide the data to the calling side (a separate query, a header row in the result set etc.) and use fixed column names and do the possible renaming on the client side based on the known names for each column.

Posted 14-Jan-16 9:22am

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Stephen Hewison · Accepted Answer · 2016-01-14T02:09:00

There are two ways. Using the PIVOT command or using dynamic SQL and sp_ExecuteSQL.

If you use dynamic SQL make sure you pass all your parameters into it's execution as parameters to negate SQL injection vulnerabilities.

Someone has answered almost this exact question using the dynamic SQL method here:

How to get Dynamic Column names in SELECT statement[^]

For help using the PIVOT see the following:

sql - PIVOT with MONTH() - Stack Overflow[^]