How to making the column name dynamic....

Question

How to making the column name dynamic....

only4mithunc

SSCrazy Eights

Points: 8434
More actions
July 1, 2009 at 11:37 am

#384896

Hi all,
Is there any way to make the column name dynamic in SQL 2005 ? The situation is in a select statement there is one case statement , based on the value of a variable the name of the column should change.
Eg :
DECLARE @v-2 VARCHAR(30)
SET @v-2 ='MONTH'
SELECT
SUM( CASE @v-2 WHEN @v-2 = 'MONTH' THEN C1 ELSE C2 END) AS NAME_@v
FROM TABLE NAME
Note: C1 and C2 are the column names.
Issue: Here the variable @v-2 can have value either 'MONTH' or 'YEAR' , so based on this value I wanted to get the name of the column as NAME_MONTH or NAME_YEAR.
Is there any way to get it done, WITH OUT USING DYNAMIC QUERY , I mean with out using exec(@sql), or exec sp_executesql @sql
Thansk in advance...
Regards,
MC
Thanks & Regards,
MC

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

What you are going to need to look into is dynamic sql. I suggest that your start first with looking it up in BOL (Books Online, the SQL Server Help System). You can access BOL from SSMS (SQL Server Management Studio) by pressing the {f1} function key.

If, after reading about dynamic sql, you still have questions we will be here to help clarify and improve your understanding.

WayneS SSC Guru Points: 95410 More actions · Answer 2

only4mithunc (7/1/2009)
Hi all,
Is there any way to make the column name dynamic in SQL 2005 ? The situation is in a select statement there is one case statement , based on the value of a variable the name of the column should change.
Eg :
DECLARE @v-2 VARCHAR(30)
SET @v-2 ='MONTH'
SELECT
SUM( CASE @v-2 WHEN @v-2 = 'MONTH' THEN C1 ELSE C2 END) AS NAME_@v
FROM TABLE NAME
Note: C1 and C2 are the column names.
Issue: Here the variable @v-2 can have value either 'MONTH' or 'YEAR' , so based on this value I wanted to get the name of the column as NAME_MONTH or NAME_YEAR.
Is there any way to get it done, WITH OUT USING DYNAMIC QUERY , I mean with out using exec(@sql), or exec sp_executesql @sql
Thansk in advance...
Regards,
MC

MC,

As Lynn stated, one way is to use dynamic sql.

If @v-2 will only ever equal one of a few values, there is another way:

if @v-2 = 'MONTH'

SELECT SUM(C1) AS NAME_MONTH

FROM TABLE NAME

else if @v-2 = 'YEAR'

SELECT SUM(C2) AS NAME_YEAR

FROM TABLE NAME

Note that from an optimizer / execution plan reuse point of view, it would be better to have each select in it's own procedure. Your code then would change to:

if @v-2 = 'MONTH' execute MyProcMonth

else if @v-2 = 'YEAR' execute MyProcYear

where MyProcMonth contains the query for month, and MyProcYear contains the query for year.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 3

Hi thanks...,

So if Im not using dynamic sql , "IF... ESLE " is the option to solve the bellow mentioned issue ? or it is the one among the solutions..? 🙂

Regards,

Mithun C

Thanks & Regards,
MC

WayneS SSC Guru Points: 95410 More actions · Answer 4

Pretty much. I can't think of any other way; perhaps someone else will pipe in with an alternate suggestion.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 5

Ok.... any way once again thanks a lot...

Regards

MC

Thanks & Regards,
MC

WayneS SSC Guru Points: 95410 More actions · Answer 6

only4mithunc (7/3/2009)
Ok.... any way once again thanks a lot...
Regards
MC

I'm glad that we could help you.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2