Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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:01am
Edited 15-Sep-12 9:40am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

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?
  Permalink  
Comments
losmac at 16-Sep-12 15:49pm
   
Interesting link. +5
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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  
Comments
losmac at 16-Sep-12 15:50pm
   
Agree, 5!
Sandeep Mewara at 17-Sep-12 1:22am
   
Thanks Mac. :)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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  
Comments
Sandeep Mewara at 17-Sep-12 1:22am
   
Thanks for filling in the gap! 5! :)
losmac at 17-Sep-12 12:31pm
   
You're welcome ;)

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



Advertise | Privacy | Mobile
Web03 | 2.8.141022.1 | Last Updated 16 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100