3

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Furkan Gözükara
  • 22,964
  • 77
  • 205
  • 342

2 Answers2

11
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

Community
  • 1
  • 1
juergen d
  • 201,996
  • 37
  • 293
  • 362
1

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • @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 – RichardTheKiwi Oct 07 '12 at 18:49
  • @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. – Gordon Linoff Oct 07 '12 at 18:52
  • the following part of the question can you check ? http://stackoverflow.com/questions/12771906/how-to-replace-a-column-values-when-querying-with-left-join-command-mssql-2008-r – Furkan Gözükara Oct 07 '12 at 19:07