title: Dynamic pivot multiple columns
body: <p>I have a table with results like this: </p>
  
  <pre><code>EMP_ID  | Boss_ID | Boss_Name | Specialty
  
    1001     001      John             sql
    1001     001      John             c#
    1002     002      James            c++
    1002     003      Sarah            sql
    1002     003      Sarah            python
    1003     004      Jesse            networking
  </code></pre>
  
  <p>Employees can have multiple bosses which can have multiple specialties. </p>
  
  <p>I need to dynamically pivot the data so that I have one line per employee. I need to display the employee, all the bosses they have and just their line 1 specialty.  </p>
  
  <p>I am able to use dynamic pivot and display the multiple bosses part but I am unsure of how to display their specialty next to their name. </p>
  
  <p>Here is what I have: </p>
  
  <pre><code>   DECLARE @cols        AS NVARCHAR(max)
            ,@query       AS NVARCHAR(max)
  
     select t.EMP_ID 
           ,t.Boss_Name
           ,t.Boss_ID
           ,t.Specialty
           ,'BOSS' + '_' + cast(ROW_NUMBER() OVER (PARTITION BY t.EMP_ID ORDER BY t.BOSS_ID asc) AS VARCHAR) AS b_rn
           ,'SPEC' + '_' + cast(ROW_NUMBER() OVER (PARTITION BY t.Specialty ORDER BY t.BOSS_ID asc) AS VARCHAR) AS spec_rn
     INTO #work 
     from #testing t
  
  SELECT @cols = STUFF(
                    (SELECT DISTINCT TOP 100 PERCENT '],[' + w.b_rn  
                     FROM #work w
                     ORDER BY '],[' + w.b_rn
                     FOR XML PATH('') 
                    ), 1, 2, ''
                  ) + ']'
  
  PRINT @cols
  
  SET @query = N' SELECT EMP_ID,' + @cols + N' 
              INTO ##work_results FROM 
                   (
                    SELECT EMP_ID
                          ,Boss_Name
                          ,b_rn 
  
                    FROM #work
                    ) AS sourcetable 
  PIVOT
  (
   max(Boss_Name) for b_rn IN ('+ @cols + N') 
   ) AS pivot_table'
  
  
  execute(@query)
  
  select * from ##work_results
  </code></pre>
  
  <p>This gives me: </p>
  
  <pre><code>EMP_ID  |   BOSS_1  |   BOSS_2  |   BOSS_3
  1001        John       John          NULL
  1002        James      Sarah         Sarah
  1003        Jesse      NULL          NULL
  </code></pre>
  
  <p>What I am really looking for is results like this: </p>
  
  <pre><code>  EMP_ID | Boss_1 | Specialty_1 | Boss_2 | Specialty_1   
  
    1001     John     sql 
    1002     James    c++           Sarah      sql
    1003     Jesse    networking
  </code></pre>
  
  <p>I have looked at other posts but they aren't quite what I am looking for. </p>
  
  <p>Any help would be wonderful. </p>
  
  <p>Thanks!</p>