Click here to Skip to main content
Search within:



Next

how to assign alias of column name from using another table column values
Sign Up to vote bad
good
See more: SQL
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:
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:01
sangamesh arali1K
Edited 15 Sep '12 - 9:40
Sandeep Mewara473K
v2
Add a Solution


3 solutions

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?
  Permalink  
Posted 15 Sep '12 - 9:48
Zoltán Zörgő39.6K
Comments
losmac - 16 Sep '12 - 15:49
Interesting link. +5
Not directly using a SELECT statement. You can use dynamic SQL like:
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.
  Permalink  
Posted 15 Sep '12 - 9:51
Sandeep Mewara473K
Comments
losmac - 16 Sep '12 - 15:50
Agree, 5!
Sandeep Mewara - 17 Sep '12 - 1:22
Thanks Mac. :)
As Sandeep Mewara wrote, you need to create GetName function:
--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[^]
  Permalink  
Posted 16 Sep '12 - 10:04
Maciej Los54.8K
Comments
Sandeep Mewara - 17 Sep '12 - 1:22
Thanks for filling in the gap! 5! :)
losmac - 17 Sep '12 - 12:31
You're welcome ;)
Add a Solution

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 OriginalGriff 273
1 Sergey Alexandrovich Kryukov 208
2 Abhinav S 168
3 Abhishek Pant 145
4 Guirec Le Bars 120
0 Sergey Alexandrovich Kryukov 8,439
1 OriginalGriff 6,681
2 CPallini 3,553
3 Rohan Leuva 2,793
4 Maciej Los 2,234


Advertise | Privacy | Mobile
Web01 | 2.6.130516.1 | Last Updated 16 Sep 2012
Copyright © CodeProject, 1999-2013
All Rights Reserved. Terms of Use
Layout: fixed | fluid