Re: Rename column name using variable

Jane T (janet@xxxxxxxxxx) writes:
I don't quite know the correct question to ask so I have had to phrase it
like a simpleton.

I had hoped it would be this simple but obviously it isn't.

Can someone help me out.


DECLARE @xxx VARCHAR(1)
SELECT @xxx = 'X'

SELECT
column1 AS @xxx
FROM
table1

The reason you cannot do this is that a SELECT statement returns a table,
and a table has a fixed set of columns, including their names and data
types.

I'm skeptic to that the right place to do this is in SQL, but you could
save the data into a temp-table, and the use sp_rename to rename the column,
and then use SELECT * to get the data:

create table #tempis(a int not null,
b int not null)

insert #tempis (a, b) values (1, 9)

declare @mynewname sysname
select @mynewname = 'fritte'


exec tempdb..sp_rename '#tempis.a', @mynewname, 'COLUMN'

select * from #tempis
go
drop table #tempis

Beware that this will cause some recompiles, which on SQL 2005 is not
such a big deal, because the recompilation is on statement level. On the
SQL 2000 the entire procedure is recompiled which can be costly if
the procedure is long.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages