create column alias with concatenation.

Question

create column alias with concatenation.

abhas

SSCarpal Tunnel

Points: 4681
More actions
September 16, 2014 at 1:31 am

#397349

Hi guys,
I want to create column name dynamically in select list.
create table #temp(name varchar(10), sale int)
insert into #temp values('john',1000)
insert into #temp values('Mike',500)
insert into #temp values('Abhas',200)
select name,sale as sale from #temp
Now i want change column alias only , not value. I need to concatenate sale with year value of getdate(). i.e. column header concatenation only, not a output value.
so my output would be as
name Sales2014
john 1000
Mike 5000
Abhas 2000
Thanks.
Abhas.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

If you want it to be truly dynamic and decide on the column's name during runtime, you can do it with dynamic code. Notice that dynamic code has its price especially regarding to security. If you'll explain a little as for why you need this, maybe some of us will come with a better approach. In any case Bellow is a way to do it with dynamic SQL

create table #temp(name varchar(10), sale int)

insert into #temp values('john',1000)

insert into #temp values('Mike',500)

insert into #temp values('Abhas',200)

declare @sql varchar(200)

select @sql = concat('select name, sale as sale',year(getdate()),' from #temp')

exec (@sql)

drop table #temp

Adi

Edit:

Only after posting the answer I've noticed that it was asked for SQL Server 2008. This means that you can't use the concat function. You can still use the same way, but instead of working with concat function, you'll have to use the plus sign (+) and cast the results of year function as varchar.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 2

Thanks Adi,

I am getting exactly same error.

Where i need to use (+) sign. Do you mean instead of concat function?

Thanks,

Abhas.

done by below idea:

create table #temp(name varchar(10), sale int)

insert into #temp values('john',1000)

insert into #temp values('Mike',500)

insert into #temp values('Abhas',200)

declare @yr varchar(20)

select @yr = year(getdate())

declare @sql varchar(200)

select @sql = ('select name, sale as sale'+''+@yr+''+ ' from #temp')

exec (@sql)

drop table #temp

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 3

abhas (9/16/2014)
Thanks Adi,
I am getting exactly same error.
Where i need to use (+) sign. Do you mean instead of concat function?
Thanks,
Abhas.
done by below idea:
create table #temp(name varchar(10), sale int)
insert into #temp values('john',1000)
insert into #temp values('Mike',500)
insert into #temp values('Abhas',200)
declare @yr varchar(20)
select @yr = year(getdate())
declare @sql varchar(200)
select @sql = ('select name, sale as sale'+''+@yr+''+ ' from #temp')
exec (@sql)
drop table #temp

Instead of executing the dynamic SQL, print it and you'll see what the problem is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101117 More actions · Answer 4

Another option is to rename the column(s) then use "SELECT *" for output:

--CREATE TABLER #temp ...

--INSERT INTO #temp ... ...

DECLARE @sql varchar(8000)

SET @sql = 'EXEC tempdb.sys.sp_rename ''#temp.sale'', ''Sales' + CAST(YEAR(GETDATE()) AS varchar(4)) + ''', ''COLUMN'''

EXEC(@sql)

SELECT *

FROM #temp

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".