CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2005select
thevan
thevan

Reputation: 10344

How to select dynamic column value from a table?

I have a table called "TableA" which has the following records:

TableA:

      ID       Jan       Feb
     ----     -----     -------
      1      '01/12'    '04/12'

Here I want to select any one of the column value from the table. But the column name is assigned to a variable. We don't know the exact column name.

For Example:

    Declare @Month VARCHAR(20)
    SET @Month = 'Feb'

    Select @Month from TableA

It gives the Output as follows:

    'Feb'

But the Desired Output is '04/12'

How to get the desired output?

Upvotes: 2

Views: 14105

Answers (4)

podiluska
podiluska

Reputation: 51494

Use UNPIVOT

select months
from TableA
unpivot 
(months for m in (Jan, Feb)) pvt
where m=@month

It's a much safer solution than dynamic SQL as it isn't vulnerable to a SQL Injection attack

Upvotes: 6

StuartLC
StuartLC

Reputation: 107237

Use EXEC or sp_executesql

EXEC:

Declare @Month NVARCHAR(20)
SET @Month = 'Feb'
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'Select ' + @Month + ' from TableA'
EXEC(@SQL)

sp_executesql is preferable, as it parameterizes the variable:

DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'Select @Month from TableA'
EXEC sp_executesql @SQL, 
              N'@Month NVARCHAR(20)',
              @Month = 'Feb'

(This just returns the constant 'Feb' - column names can't be variables )

Upvotes: 2

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

Declare @Month VARCHAR(20)
     SET @Month = QUOTENAME('Feb')
 exec('Select '+@Month+' from TableA')

Upvotes: 5

yogi
yogi

Reputation: 19591

Try this

Declare @colName VARCHAR(20)
SET @colName = 'Feb'

Exec('select '+ @colName+' from TableA')

Upvotes: 1

Related Questions