is lookup function available in SQL server

Question

I am trying to do the following task. I have 2 tables with following data?

Table1 :

objectid    name          LOCATION   Project Manager

4584     Client             NULL     Indicator

4591     Ericsson           NULL     Indicator

4604     NEW201             Text     NULL

Table2:

projectobjectid     name      value

4584             Indicator    Red

4591             Indicator    Green

4604             Text         Sf001-bb1-5

4604             Text         Sf001-bb1-4

4604             Text         Sf001-bb1-3

4604             Text         Sf001-bb1-2

result as follows:

objectid    name                     LOCATION        Project Manager

4584        Client                     NULL              Red

4591        Ericsson                    NULL             Green

4604         NEW201                  Sf001-bb1-5          NULL

4604         NEW201                  Sf001-bb1-4          NULL

4604         NEW201                  Sf001-bb1-3          NULL

4604         NEW201                  Sf001-bb1-2          NULL

Update Table 1 from Table 2.

Answer 1

You can do this with two left joins, one for each column you want to replace:

SELECT    t1.objectid, t1.name, l1.value, l2.value
FROM      table1 t1
LEFT JOIN table2 l1 ON t1.objectid = l1.projectobjectid AND 
                       t1.location = l1.name
LEFT JOIN table2 l2 ON t1.objectid = l2.projectobjectid AND 
                       t1.projectmanager = l2.name

SQL Fiddle Demo

Answer 2

There you go (with only one left join):

SELECT t1.objectid, t1.name, 
CASE t1.location WHEN t2.name THEN t2.value END as Location, 
CASE t1.projectmanager WHEN t2.name THEN t2.value END as ProjectManager
FROM table1 t1
LEFT JOIN table2 t2 ON t2.projectobjectid=t1.objectid

SQL Fiddle Demo