Changing the column name dynamically

Question

Changing the column name dynamically

koti.raavi

SSCrazy

Points: 2756
More actions
December 21, 2015 at 10:36 pm

#323523

Hi All,
Below is the sample query
Select Month1,Month2,Month3......Month24 from #temp
I need to display data like below
Select Month1 as [Dec 2015], Month2 as [Jan 2016],Month3 as [Feb 2016] From #temp
Any questions please let me know , Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

twin.devil SSC-Insane Points: 22208 More actions · Answer 1

when you are creating the temp table you must be knowing which month it is right. If yes then share us the schema of your SP so that this could be done. By the way when you are creating the Temp table. why are you not making the month name column at that particular time.

for a simple hint you have to do it using dynamic sql.

koti.raavi SSCrazy Points: 2756 More actions · Answer 2

can you please give small example how to write dynamic query ...Thanks

twin.devil SSC-Insane Points: 22208 More actions · Answer 3

As you didnt share the your query i have to build a prototype query to generate the list of columns for u.

This query required two parameters i-e StartDate, EndDate. Rest of the query is below.

/*

To generate Comma-separated list of column dynamically script

*/

-- Declare parameters to get the total list of columns to be shown.

DECLARE @StartDate datetime= '2015-01-01'

, @EndDate datetime= '2016-12-01'

, @Query nvarchar(max)= ''---- This will hold the dynamic sql query

;

SELECT @Query = 'SELECT ' +

--- This function is used to remove the addition ", " in the comma-separated column list

STUFF(

--- This section will transform the rows into a comma-separated list of columns

(

SELECT

', [Month' + Cast(a.number as varchar(5))+ '] AS [' + rtrim(ltrim(Right(Convert(nvarchar(12), a.DateOfMonth, 113), 10))) + ']'

FROM

(

---- This section of the query will generate the list Month

SELECT TOP (DateDiff(mm, @StartDate, @EndDate) + 1)

v.number + 1 AS number

, dateadd(mm, v.number, @StartDate) as DateOfMonth

FROM master.dbo.spt_values v---- this is used as 'Tally Table'

WHERE

v.type = 'p'

) a

ORDER BY

a.number

FOR XML PATH (''), root('RequiredColumnName'), type).value('/RequiredColumnName[1]','nvarchar(max)')

, 1, 2, '')

+ Char(10)

+ ' From #Temp'

;

--- This will print the dynamic query.

Print (@Query)

--- Uncomment the below query if want to execute the dynamic query.

--exec sp_executesql @query

Hope it helps.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

koti.raavi (12/21/2015)
Hi All,
Below is the sample query
Select Month1,Month2,Month3......Month24 from #temp
I need to display data like below
Select Month1 as [Dec 2015], Month2 as [Jan 2016],Month3 as [Feb 2016] From #temp
Any questions please let me know , Thanks

Twin.Devil's code is fine but let's get to the actual root of the problem. What does the original data look like? I ask because, if done properly, it looks like you might be trying to do a "pivot" or CROSSTAB report.

Please see the article at the first link in my signature below under "Helpful Links" for how to post an example of table an data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

twin.devil SSC-Insane Points: 22208 More actions · Answer 5

Jeff Moden (12/22/2015)
koti.raavi (12/21/2015)
Hi All,
Below is the sample query
Select Month1,Month2,Month3......Month24 from #temp
I need to display data like below
Select Month1 as [Dec 2015], Month2 as [Jan 2016],Month3 as [Feb 2016] From #temp
Any questions please let me know , Thanks
Twin.Devil's code is fine but let's get to the actual root of the problem. What does the original data look like? I ask because, if done properly, it looks like you might be trying to do a "pivot" or CROSSTAB report.
Please see the article at the first link in my signature below under "Helpful Links" for how to post an example of table an data.

Jeff you are right about the actual root cause thing but sometimes OP required the only one thing which is missing from the puzzle. i-e OP have already done all the data transformation or CROSSTAB but unable to get to the finish line (My opinion only). As per the current problem i though OP has done the hard work just couldn't cross the finish line so i just helped him 😀

Or i could also be very very wrong about it ... BTW i have mention in my earlier reply that month should be the name of the column at first place 🙂

twin.devil SSC-Insane Points: 22208 More actions · Answer 6

Jeff Moden (12/22/2015)
Twin.Devil's code is fine ....

Thank you SIR, i also missed what you have written. i should take a snapshot of it and frame it on my wall 😎

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

twin.devil (12/22/2015)
Jeff you are right about the actual root cause thing but sometimes OP required the only one thing which is missing from the puzzle. i-e OP have already done all the data transformation or CROSSTAB but unable to get to the finish line (My opinion only). As per the current problem i though OP has done the hard work just couldn't cross the finish line so i just helped him
Or i could also be very very wrong about it ... BTW i have mention in my earlier reply that month should be the name of the column at first place

Understood. The thing is that I've seen a lot of "only one thing missing from the puzzle" posts that have a strong indication that the OP has gone down the primrose path for those things leading up to that one thing. I believe that's the case in this case. Stop and think about it... the OP is asking how to change temporally based column names in a Temp Table instead of correctly generating those names for what looks to be a "floating window" CROSSTAB report, which should actually need no Temp Table at all. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions