sql server 2008 - Dynamic Pivot and Join -

i have table representing employees , various details. have table representing training courses employees have attended. employees have attended more courses others. need represent each employee single row in returned query associated details , column each course have been on.

employee table:

 +--------+---------+---------+----------+ | empnum | empname | emptype | empphone | +--------+---------+---------+----------+ |   1234 | smith   | ft      | 09876543 | |   2345 | jones   | pt      | 08765432 | |   3456 | brown   | ft      | 07654321 | +--------+---------+---------+----------+ 

skill table:

 +--------+------------+ | empnum | skillname  | +--------+------------+ |   1234 | first aid  | |   2345 | first aid  | |   3456 | first aid  | |   1234 | safety     | |   3456 | safety     | |   1234 | leadership | |   2345 | leadership | +--------+------------+ 

after joining these 2 tables, i'm looking have following returned:

 +--------+---------+---------+----------+-----------+------------+------------+--------+ | empnum | empname | emptype | empphone |  skill1   |   skill2   |   skill3   | skill4 | +--------+---------+---------+----------+-----------+------------+------------+--------+ |   1234 | smith   | ft      | 09876543 | first aid | safety     | leadership |        | |   2345 | jones   | pt      | 08765432 | first aid | leadership |            |        | |   3456 | brown   | ft      | 07654321 | first aid | safety     |            |        | +--------+---------+---------+----------+-----------+------------+------------+--------+ 

what you're looking dynamic crosstab. read article jeff moden more information.

sql fiddle

declare @sql1 nvarchar(2000) = '',         @sql2 nvarchar(2000) = '',         @sql3 nvarchar(2000) = ''  declare @range int  --added +1 here since have skill4 on expected result select top 1 @range = count(*) + 1  skill group empnum order count(*) desc  select @sql1 = 'select     e.empnum     , e.empname     , e.emptype     , e.empphone' + char(10)  select @sql2 = @sql2 + '   , max(case when rn = ' + convert(varchar(3), rn) + ' s.skillname else '''' end) '      + quotename('skill' + convert(varchar(3), rn))+ char(10) from(     select top (@range)          row_number() on (order (select null)) rn     master..spt_values )t  select @sql3 = 'from(     select *, rn = row_number() over(partition empnum order (select null))     skill )s inner join employee e     on e.empnum = s.empnum group     e.empnum, e.empname, e.emptype, e.empphone'  print(@sql1 + @sql2 + @sql3) exec (@sql1 + @sql2 + @sql3)