how to assign alias of column name from using another table column values

Question

0.00/5 (No votes)

See more:

Hi,

I have doubt for writing query in sql for assigning alias of column names using another column values that i mentioned below:

Table1 
Id     FName   Lname
1      peter    pt
2      shiv     sh

Table2
attribute     Displayname
FName         FirstName
Lname         LastName

So the output what i want is like below

Id    FirstName      LastName
1      peter           pt
2      shiv            sh

I tried for using below query but i dint get it:

SQL

select Id,FName as (select Displayname from Table2 where attribute='FName'),
Lname as (select Displayname from Table2 where attribute='Lname')
from Table1

Please help me out

Posted 15-Sep-12 7:01am

sangamesh arali

Updated 15-Sep-12 9:40am

Sandeep Mewara

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Answer 1 · 2012-09-15T09:48:00

Solution 1

You can not do this in a single t-sql statement. There are other ways, like described here: http://pratchev.blogspot.hu/2008/02/column-alias-based-on-variable.html[^]
But why do you need to do this? Is there no other layer above the database where you can programatically assign field label?

Posted 15-Sep-12 9:48am

Zoltán Zörgő

Comments

Maciej Los 16-Sep-12 15:49pm

Interesting link. +5

Sandeep Mewara · Answer 2 · 2012-09-15T09:51:00

Solution 2

Not directly using a SELECT statement. You can use dynamic SQL like:

SQL

set @sql = N'SELECT Column1 as ' + GetName(@myVariable) + N' FROM Table1'
exec sp_executesql @sql

But this is not suggestible as dynamic SQL has security implications/management issues.

Posted 15-Sep-12 9:51am

Sandeep Mewara

Comments

Maciej Los 16-Sep-12 15:50pm

Agree, 5!

Sandeep Mewara 17-Sep-12 1:22am

Thanks Mac. :)

Maciej Los · Answer 3 · 2012-09-16T10:04:00

Solution 3

As Sandeep Mewara wrote, you need to create GetName function:

SQL

--input parameters: @AttribName


--variable to store description
DECLARE @Description VARCHAR(50)

--get description by attribute name
SELECT @Description = Displayname
FROM Table2
WHERE Attribute = @AttribName

RETURN @Description

Please, read more at: http://msdn.microsoft.com/en-us/library/ms186755.aspx[^]

Posted 16-Sep-12 10:04am

Maciej Los

Comments

Sandeep Mewara 17-Sep-12 1:22am

Thanks for filling in the gap! 5! :)

Maciej Los 17-Sep-12 12:31pm

You're welcome ;)