title: Combine multiple rows into multiple columns dynamically in SQL Server
body: <p>I have a large database table on which I need to perform the action below dynamically using Microsoft SQL Server.</p>
  
  <p>From a result like this:</p>
  
  <pre><code> badge   |   name   |   Job   |   KDA   |   Match 
   - - - - - - - - - - - - - - - -
   T996    |  Darrien |   AP    |   3.0   |   20
   T996    |  Darrien |   ADC   |   2.8   |   16
   T996    |  Darrien |   TOP   |   5.0   |   120
  </code></pre>
  
  <p>To a result like this using SQL:</p>
  
  <pre><code>badge   |   name   |  AP_KDA | AP_Match | ADC_KDA | ADC_Match | TOP_KDA | TOP_Match 
  - - - - - - - - -
  T996    |  Darrien |   3.0   |   20     |  2.8    |   16      |   5.0   |  120      
  </code></pre>
  
  <p>Even if there are 30 rows, it also will combine into a single row with 60 columns.</p>
  
  <p>I am currently able to do it by hard coding (see  the example below), but not dynamically.</p>
  
  <pre><code>Select badge,name,
  (
   SELECT max(KDA)
   FROM table
   WHERE (h.badge = badge) AND (h.name = name) 
   AND (Job = 'AP')
  ) AP_KDA,
  (
   SELECT max(Match)
   FROM table
   WHERE (h.badge = badge) AND (h.name = name) 
   AND (Job = 'AP')
  ) AP_Match,
  (
   SELECT max(KDA)
   FROM table
   WHERE (h.badge = badge) AND (h.name = name) 
   AND (Job = 'ADC')
  ) ADC_KDA,
  (
   SELECT max(Match)
   FROM table
   WHERE (h.badge = badge) AND (h.name = name) 
   AND (Job = 'ADC')
  ) ADC_Match,
  (
   SELECT max(KDA)
   FROM table
   WHERE (h.badge = badge) AND (h.name = name) 
   AND (Job = 'TOP')
  ) TOP_KDA,
  (
   SELECT max(Match)
   FROM table
   WHERE (h.badge = badge) AND (h.name = name) 
   AND (Job = 'TOP')
  ) TOP_Match
  from table h
  </code></pre>
  
  <p>I need an MSSQL statement that allows me to combine multiple rows into one row. The column 3 (<code>Job</code>) content will combine with the column 4 and 5 headers (<code>KDA</code> and <code>Match</code>) and become a new column.</p>
  
  <p>So, if there are 6 distinct values for <code>Job</code> (say <code>Job1</code> through <code>Job6</code>), then the result will have 12 columns, e.g.: <code>Job1_KDA</code>, <code>Job1_Match</code>, <code>Job2_KDA</code>, <code>Job2_Match</code>, etc., grouped by badge and name.</p>
  
  <p>I need a statement that that can loop through the column 3 data so I don't need to hardcode (repeat the query for each possible <code>Job</code> value) or use a temp table.</p>