title: Join table rows as column values sql
body: <p>In this situation I have two tables that I have joined, and it creates multiple duplicates due to each physician being able to have multiple licenses. I would like to pivot and make the multiple license state and numbers additional columns to table 1 as shown below.</p>
  
  <p>This will need to be dynamically done since it can be an substantial amount of licenses assigned to one physician.</p>
  
  <p>Table 1 looks like this.</p>
  
  <pre><code>assignid  physician_name  profession 
  -------------------------------
      1        bob          md        
      2        travis       do        
      3        ryan         md        
      4        pete         pa        
      5        susan        np         
      6        ashley       cnp     
  </code></pre>
  
  <p>Table 2   </p>
  
  <pre><code>assignid  license_state license_num
  -------------------------------
       1       oh        561
       2       mi        987
       3       ca        785
       4       ny        965
       4       mi        125
       5       oh        369
       5       ca        541
  </code></pre>
  
  <p>Joined</p>
  
  <pre><code>assignid  physician_name  profession license_state license_num
  ----------------------------------------------------------------
      1        bob          md             oh           561
      2        travis       do             mi           987
      3        ryan         md             ca           785
      4        pete         pa             ny           965
      4        pete         pa             mi           125
      5        susan        np             oh           369
      5        susan        np             ca           541
  </code></pre>
  
  <p>I want to dynamically change the joined table to look like this.</p>
  
  <pre><code>assignid  physician_name  profession license_state1 license_num1 license_state2 license_num2
  --------------------------------------------------------------------------------------------------
     1        bob          md             oh           561
     2        travis       do             mi           987
     3        ryan         md             ca           785
     4        pete         pa             ny           965             mi       125
     5        susan        np             oh           369             ca       541
  </code></pre>
  
  <p>I attempted this route, but this gives me states as column header.</p>
  
  <pre><code>WITH pivotdata AS (
  SELECT assignid,physician_name, profession, license_state,license_num
  FROM dbo.Physicians p JOIN dbo.Licenses l ON p.AssignID = l.AssignID
  )
  
  SELECT *
  FROM
     pivotdata
     PIVOT (MAX(license_num) FOR license_state IN ([oh], [mi], [ca],[ny])) TT;
  </code></pre>
  
  <p>Results</p>
  
  <pre><code>physician_name  profession   oh  mi  ca  ny
  -------------------------------------------
      bob          md          561
      travis       do             987
      ryan         md                  785
      pete         pa             125     965
      susan        np          369     541
  </code></pre>