Rename column name using variable

[Jane T]

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

[Plamen Ratchev]

This can be easily done in your reporting or front-end interface, not sure
why trying to do it in T-SQL. You cannot directly use a variable for column
alias. Here are two methods:

CREATE TABLE Foo (
keycol INT PRIMARY KEY,
datacol CHAR(1))

INSERT INTO Foo VALUES (1, 'a')
INSERT INTO Foo VALUES (2, 'b')

DECLARE @column_alias VARCHAR(30)
SET @column_alias = 'new_title'

-- 1). Using dynamic SQL
DECLARE @sql VARCHAR(200)

SET @sql = 'SELECT keycol, datacol AS ' + @column_alias + ' FROM Foo'

EXEC sp_sqlexec @sql

-- 2). Using results table and renaming the column
CREATE TABLE Results (
keycol INT PRIMARY KEY,
datacol CHAR(1))

INSERT INTO Results
SELECT keycol, datacol
FROM Foo

EXEC sp_rename 'Results.datacol', @column_alias, 'COLUMN'

SELECT * FROM Results

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[--CELKO--]

Why do you want to do this? Let the host program give it a display
name
. You seem to be missing the idea of RDBMS and data modeling. The
data element names have meaning and you don't get to change them on
the fly. Height cannot suddenly become weight, etc.

[Erland Sommarskog]

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, esq...@sommarskog.se

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