labeling a column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

labeling a column

Author

Topic

redss
Starting Member

6 Posts

Posted - 2005-08-11 : 11:26:53

Is there anyway to name a column dynamically from a character variable?
Something like this:

declare @colname varchar(20)
set @colname = 'mycolumn'
SELECT col1 @colname, col2 col2name

mycolumn col2
-------- ----
1 2

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-11 : 11:34:45

not without using dynamic sql.

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-12 : 01:41:40

>>Is there anyway to name a column dynamically from a character variable?

Why dynamic name?

Instead you can use alias name
If the value assigned to @colname is the name of the other column, you will have diificulties to access that column

Madhivanan

Failing to plan is Planning to fail

redss
Starting Member

6 Posts

Posted - 2005-08-14 : 14:01:06

madhivanan, what is the syntax of the alias name you are referring to?
I get a syntax error when I try to use a column alias, e.g. the following won't work:
select @colname = 'myname'
select realcolname as @colname from mytable

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-08-14 : 22:13:40

I don't know why you need this.

I think you have to use dynamic SQL like this:-

declare @colname as varchar(10)
select @colname = 'myname'
exec ('select realcolname as ' + @colname + ' from mytable')

cheers

mk_garg

redss
Starting Member

6 Posts

Posted - 2005-08-15 : 16:21:28

Thanks for the response. Problem is the dynamic sql breaks when I add a table variable...

declare @mytable table ( realcolname varchar(10) )
declare @colname as varchar(10)
select @colname = 'myname'

then neither of these work:
exec ('select realcolname as ' + @colname + ' from ' + @mytable)
exec ('select realcolname as ' + @colname + ' from @mytable')

redss
Starting Member

6 Posts

Posted - 2005-08-15 : 16:23:00

I should mention, the reason why I want to do this, is because this is for a report where they would like to see an actual date as the header for the column (e.g. "total for Aug 15" )

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-08-15 : 18:11:15

what error you are getting?

mk_garg

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-16 : 00:52:29

quote:
Originally posted by redss

I should mention, the reason why I want to do this, is because this is for a report where they would like to see an actual date as the header for the column (e.g. "total for Aug 15" )

You can use alias name

Select max(field) as "total for Aug 15" from yourtable
Group by somefield

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources