Help! don't know what to call this topic

Transact-SQL
1

I have table1 that is like this:

create table #table1(name varchar(50),
col1 varchar(50),
col2 varchar(50),
col3 varchar(50),
col4 varchar(50)
)
insert into #table1
select 'Patrick', '330', '331', ' ', '332'
select 'James', ' ', '331', ' ', '332'
select 'Allan', '330', ' ', ' ', '332'

And then another table that contains the descriptions for the 'numbers' in table1.

create table #table2(code varchar(50),
desc varchar(50),
)
insert into #table1
select '330', 'descriptionfor330'
select '331', 'descriptionfor331'
select '332', 'descriptionfor332'

My query result set should be like this. Each code must be concatenated with its description.:
name col1 col2 col3 col4
Patrick 330-descriptionfor330 331-descriptionfor331 [blank] 332-descriptionfor332
James [blank] 331-descriptionfor331 [blank] 332-descriptionfor332
Allan 330-descriptionfor330 [blank] [blank] 332-descriptionfor332

Please help. Thanks.

2

Something like

Query 
select a.[name]
      ,isnull(b1.code,'') as col1
      ,isnull(b2.code,'') as col2
      ,isnull(b3.code,'') as col3
      ,isnull(b4.code,'') as col4
  from #table1 as a
       left outer join #table2 as b1
                    on b1.code=a.col1
       left outer join #table2 as b2
                    on b2.code=a.col2
       left outer join #table2 as b3
                    on b3.code=a.col3
       left outer join #table2 as b4
                    on b4.code=a.col4
;
1 Like