How to replace fetched values with another column while querying with SQL Server 2008 R2

Question

Alright let me explain my question with example

We have 1 table This table contains

Id
Name
Number

Now example

1 House 4
2 Hospital 3
3 Airport 1
4 Station 2

Now when fetching as select * from table

I want to replace third column number values with that number representing Name

So example of fetching

1 House Station
2 Hospital Airport
3 Airport House
4 Station Hospital

How can i do this ? thank you

Solution 1

select t1.id, 
       t1.name,
       t2.name as name2
from your_table t1
left join your_table t2 on t1.number = t2.id

You can join the same table twice to replace the number with the name. The on contidion in the join matches the table again and then you can select the name from that table (t2)

SQLFiddle Example

Solution 2

You can do this with an explicit join:

select t.id, t.name, t2.name as otherName
from t left outer join
     t t2
     on t2.number = t.id

Comments

[MonsterMMORPG]

could not make it work. there are not 2 different tables. it is on same table.

[juergen d]

replace your_table with your table name but net the alias names be.

[RichardTheKiwi]

@Monster FYI this is exactly the same query as juergen's, except "T" is used as the table name instead of "your_table". Both incredibly have the same issue, you shouldn't name both columns "name", which will make the 2nd hidden from some front-end IDEs, or SQLFiddle

[Gordon Linoff]

@RichardTheKiwi . . . Good point Richard. These will work in SQL Server Management Studio, but the duplicate name could be a problem in other situations. By the way, Juergen posted his answer 16 seconds before I did, so if one is accepted, it should be his.

[MonsterMMORPG]

the following part of the question can you check ? stackoverflow.com/questions/12771906/…