Is there a way

Question

Is there a way

krypto69

SSChampion

Points: 13604
More actions
February 26, 2015 at 3:02 pm

#315690

Hi,
Is there a way to display a column alias as part of the result set column labels?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Luis Cazares SSC Guru Points: 183701 More actions · Answer 1

I'm not sure what you're talking about. Can you be more explicit? Where do you want to display the column alias?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 2

select [Table Name ] = name,

[Date created]= create_date

from sys.tables

select [My current SPID]= @@spid

Grant Fritchey SSC Guru Points: 398821 More actions · Answer 3

Instead, I'd go with a more standard approach:

select name AS [Table Name],

create_date AS [Date created]

from sys.tables;

select @@spid AS [My current SPID];

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

krypto69 SSChampion Points: 13604 More actions · Answer 4

I don't think I explained this very well..let me try to do a better job.

A developer came to me yesterday and asked if he can reference the table/column alias he has created so that it is easier for him to discern.

So, the dev want to display the column alias name along with the actual column name.

I think I may have found something close:

select alias, t.COLUMN_name

from

(

select VC.COLUMN_NAME,

case when

ROW_NUMBER () OVER (

partition by C.COLUMN_NAME order by

CHARINDEX(',',VIEW_DEFINITION,CHARINDEX(C.COLUMN_NAME,VIEW_DEFINITION))-

CHARINDEX(VC.COLUMN_NAME,VIEW_DEFINITION)

) = 1

then 1

else 0 end

as lenDiff

,C.COLUMN_NAME as alias

,CHARINDEX(',',VIEW_DEFINITION,CHARINDEX(C.COLUMN_NAME,VIEW_DEFINITION)) diff1

, CHARINDEX(VC.COLUMN_NAME,VIEW_DEFINITION) diff2

from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE VC

inner join INFORMATION_SCHEMA.VIEWS V on V.TABLE_NAME = 'v'+VC.TABLE_Name

inner join information_schema.COLUMNS C on C.TABLE_NAME = 'v'+VC.TABLE_Name

where VC.TABLE_NAME = 'My_Table'

and CHARINDEX(',',VIEW_DEFINITION,CHARINDEX(C.COLUMN_NAME,VIEW_DEFINITION))-

CHARINDEX(VC.COLUMN_NAME,VIEW_DEFINITION) >0

)

t

where lenDiff = 1

Grant Fritchey SSC Guru Points: 398821 More actions · Answer 5

Yeah, a derived table or a Common Table Expression would allow you to do that. Sorry I didn't understand what you were asking.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ScottPletcher SSC Guru Points: 101046 More actions · Answer 6

You can also use CROSS APPLY to do that:

select VC.COLUMN_NAME,

case when

ROW_NUMBER () OVER (

partition by C.COLUMN_NAME order by

CHARINDEX(',',VIEW_DEFINITION,CHARINDEX(C.COLUMN_NAME,VIEW_DEFINITION))-

CHARINDEX(VC.COLUMN_NAME,VIEW_DEFINITION)

) = 1

then 1

else 0 end

as lenDiff

,alias

,CHARINDEX(',',VIEW_DEFINITION,CHARINDEX(C.COLUMN_NAME,VIEW_DEFINITION)) diff1

, CHARINDEX(VC.COLUMN_NAME,VIEW_DEFINITION) diff2

from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE VC

inner join INFORMATION_SCHEMA.VIEWS V on V.TABLE_NAME = 'v'+VC.TABLE_Name

inner join information_schema.COLUMNS C on C.TABLE_NAME = 'v'+VC.TABLE_Name

cross apply (

select C.COLUMN_NAME as alias

) as assign_alias_names

where VC.TABLE_NAME = 'My_Table'

and CHARINDEX(',',VIEW_DEFINITION,CHARINDEX(C.COLUMN_NAME,VIEW_DEFINITION))-

CHARINDEX(VC.COLUMN_NAME,VIEW_DEFINITION) >0

Edit:

What's really uber-cool is that a subsequent CROSS APPLY can use a previous alias, i.e., you can alias based on an alias:

CROSS APPLY ( SELECT a+b AS c ) AS ca1

CROSS APPLY ( SELECT c + d AS e ) AS ca2

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".

krypto69 SSChampion Points: 13604 More actions · Answer 7

Grant Fritchey (2/27/2015)
Yeah, a derived table or a Common Table Expression would allow you to do that. Sorry I didn't understand what you were asking.

my mistake Grant - I didn't explain very well. Thanks for trying.

krypto69 SSChampion Points: 13604 More actions · Answer 8

ScottPletcher (2/27/2015)
You can also use CROSS APPLY to do that:
select VC.COLUMN_NAME,
case when
ROW_NUMBER () OVER (
partition by C.COLUMN_NAME order by
CHARINDEX(',',VIEW_DEFINITION,CHARINDEX(C.COLUMN_NAME,VIEW_DEFINITION))-
CHARINDEX(VC.COLUMN_NAME,VIEW_DEFINITION)
) = 1
then 1
else 0 end
as lenDiff
,alias
,CHARINDEX(',',VIEW_DEFINITION,CHARINDEX(C.COLUMN_NAME,VIEW_DEFINITION)) diff1
, CHARINDEX(VC.COLUMN_NAME,VIEW_DEFINITION) diff2
from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE VC
inner join INFORMATION_SCHEMA.VIEWS V on V.TABLE_NAME = 'v'+VC.TABLE_Name
inner join information_schema.COLUMNS C on C.TABLE_NAME = 'v'+VC.TABLE_Name
cross apply (
select C.COLUMN_NAME as alias
) as assign_alias_names
where VC.TABLE_NAME = 'My_Table'
and CHARINDEX(',',VIEW_DEFINITION,CHARINDEX(C.COLUMN_NAME,VIEW_DEFINITION))-
CHARINDEX(VC.COLUMN_NAME,VIEW_DEFINITION) >0
Edit:
What's really uber-cool is that a subsequent CROSS APPLY can use a previous alias, i.e., you can alias based on an alias:
CROSS APPLY ( SELECT a+b AS c ) AS ca1
CROSS APPLY ( SELECT c + d AS e ) AS ca2

nice thanks Scott

Jeff Moden SSC Guru Points: 1004190 More actions · Answer 9

Grant Fritchey (2/27/2015)
Instead, I'd go with a more standard approach:

Heh... why? Portability? 😛

--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)

Grant Fritchey SSC Guru Points: 398821 More actions · Answer 10

Jeff Moden (2/27/2015)
Grant Fritchey (2/27/2015)
Instead, I'd go with a more standard approach:
Heh... why? Portability? 😛

Yes Joe.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning