create table #t1 (name varchar(20), Address varchar(20), ClaimNumber int, Phone varchar(20)) 
create table #t2 (ClaimNumber int, ClaimCode smallint) 
 
insert into #t1  
select 'Joe', '1234 Road',1, '111-1111' union all 
select 'Sue', '456 Road',2, '222-2222' union all 
select 'Mike', '78 Street',3, '333-3333' union all 
select 'Jim', '1 Main',4, '444-4444'  
 
insert into #t2 
select 1, 21 union all 
select 1, 23 union all 
select 1, 54 union all 
select 1, 26 union all 
select 1, 83 union all 
select 2, 27 union all 
select 2, 30 union all 
select 3, 54  
 
  
--PIVOT  
DECLARE @Colslist VARCHAR(MAX)  
DECLARE @Cols TABLE (Head VARCHAR(MAX))  
  
INSERT @Cols (Head)  
SELECT DISTINCT ClaimCode  
FROM #t2 
  
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'  
FROM @Cols t  
 
EXEC ('SELECT *  
FROM   
(  
        SELECT a.name, a.Address, a.ClaimNumber, a.Phone, ClaimCode 
        FROM #t1 a inner join #t2 b 
                        on a.ClaimNumber = b.ClaimNumber 
) t  
PIVOT (avg(ClaimCode) FOR ClaimCode IN (' + @ColsList + ')) PVT')