title: Convert Rows to column rows in SQL Server
body: <p>I am looking for a way to convert the rows in a table to columns rows and assign column names for each.</p>
  
  <pre><code>Players Id  Team
  john    1   t1
  carmel  1   t1
  jack    1   t1
  james   1   t1
  </code></pre>
  
  <p>Changed to:</p>
  
  <pre><code>Id  Team  p1    p2      p3      p4
  1   t1    john  carmel  jack    james
  </code></pre>
  
  <p>There can be any number of players in the above example.
  I tried using pivot. I could change the rows to columns but not in the fashion I am looking for.</p>