0

This is my query I want to set column names dynamically, is it possible through query 

select  
    branch,
    sum(case when months = 'JUL' then  [Parts Revenue Budget] end) as [JUL]
from 
    #final 
group by 
    branch

Something like this:

@Col as varchar(10);

select  
    branch,
    sum(case when months = 'JUL' then  [@Col] end) as [JUL]
from 
    #final 
group by 
    branch

I get this below error message

Warning: Null value is eliminated by an aggregate or other SET operation.

Msg 207, Level 16, State 3, Procedure CRM_FRN_PL_BR_MN, Line 215
Invalid column name @Col

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sanya Zahid
  • 466
  • 6
  • 18

2 Answers2

2

What you want to do is called dynamic SQL. It is supported by most DBMSs (you are not stating which one you are using though).

Normally, the process is to PREPARE a statement which is passed as a string, something like:

SER @Col_Name = 'Col_15' ;
SET @MySTTMNT = CONCAT('SELECT '           ,
                        @Col_Name     , ' ',
                       'FROM MyTable '     ,
                       'WHERE ID = '       ,
                       @MyVariable
                      ) ;

This string is then passed to a Prepare statement and then executed.

Check your DBMSs documentation for Dynamic SQL.

FDavidov
  • 3,505
  • 6
  • 23
  • 59
2
declare @col varchar(10);
declare @sql nvarchar(max);

set @col = 'some_field';
set @sql =  'select ' + @col + ' from #final';

exec sp_executesql @sql;
Sergey Gornostaev
  • 7,596
  • 3
  • 27
  • 39