using a same column in place of another

Question

I have a table which have 3 fields: id, name, reporting to which have other ids:

ID    NAME   REPORT
-------------------
1     asd     3
2     vv      1
3     dv      2

I want name to be printed in place of ids in the report column:

ID    NAME   REPORT
-------------------
1     asd    dv
2     vv     asd
3     dv     vv

Can somebody help?

Answer 1

You need to join on to the table that contains your report names using the report id.

Something like this:

Select a.Id,
       a.Name,
        b.Name as Report

From dbo.myTable a
Join dbo.myTable b on a.Report = b.Id

I should add that if some Names don't have a corresponding report id then you may want to use a Left Join instead of a Join so that you still get all rows in the base table.

e.g

Select a.Id,
       a.Name,
        IsNull(b.Name, 'No Report Defined') as Report

From dbo.myTable a
Left Join dbo.myTable b on a.Report = b.Id