Column name variable 1

[ablackwe]

Does any one know how you can set a variable with a column name then call it in a select statement?

select * from ksa_running_totals
DECLARE @week1 as datetime
SET @week1 = 'col' & GETDATE()

This code works OK to get the column name. What I need to do now is do a select which selects this column plus other columns.

SELECT col1, col2, @week1
FROM table1

This code doesn't work as it just puts the contents of the variable in a new column on each row.

Can anyone help
Cheers
Adam

 

[JamesLean]

You need to use dynamic SQL:

DECLARE @col_name varchar(20),
  @sql varchar(200)

SET @col_name = 'variable_col'

SET @sql = 'SELECT col1, col2, ' + @col_name + ' FROM t1'

EXEC(@sql)

--James

 

[donutman]

It's better to design the database so that you do not have columns that represent time periods. As time marches on the current design will break down. Additionally, such a design tends to encourage the use of dynamic SQL which is also poor practice.
The simple solution is to put the date specific data in one column and the date for which it applies in another column. Then you can create a view to display the results in a crosstab format thereby having the best of both worlds.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

The select statement you showed us is fine. Perhaps you should show us what result you would like to get. I'm not understanding why the select you have is not what you want.

Questions about posting. See faq183-874