Dynamically naming a column name

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)

Dynamically naming a column name

Author

Topic

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-12-19 : 13:48:13

Hello all...

I'm trying to run a select statement that will give me total dollars for a particular day. I would like the results to display like this:

Total for 12/18/02
------------------
$150,000.00

Can someone tell me how to generate the colomn name using something like this?

select 'Total for '+(select convert(varchar(10),getdate()-1,1))

Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-19 : 13:54:44

declare @sql varchar(1000)
select @sql = 'select [Total for '+(select convert(varchar(10),getdate()-1,1)) + '] = colname from tbl'

exec (@sql)

look up dynamic sql, exec and sp_executesql

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-12-19 : 13:59:09

Sorry, but you kind of lost me...

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-12-19 : 14:01:10

If I copy and paste that statement in query analyzer, it doesn't run...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-19 : 14:15:00

Why do you need to do this? You will never be able to use the result of the SELECT consistently because the field name will always change!

If you are trying to format stuff so it looks nice in the query analyzer, remember, it's NOT a reporting tool, it's a development tool.

If you want to be able to see exactly which day you are returning:

SELECT Date as [Total For], Amount
FROM Table

which should return:


Total For            Amount
------------         -------------
12/18/02             $150,000

If you want to see it in the format you specified, do it with Access or a report designer or PRINT statements in T-SQL.

- Jeff

Edited by - jsmith8858 on 12/19/2002 14:15:49

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources