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

Answer 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

Answer 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