title: SQL Server - Pivot Convert rows to columns (with additional row data)
body: <p>Can someone help me with this SQL?</p>
  
  <p>I am looking to pivot multiple rows into columns by: id, type, color, date</p>
  
  <p>Challenge: I have multiple columns I want to preserve/be shown on the pivoted table.</p>
  
  <pre><code>id | type   | color  | date      | country_code | cost
  ---+--------+--------+-----------+--------------+-------
  1  | report | red    | 2020-09-01| US           | 1
  1  | report | red    | 2020-09-01| EU           | 2
  1  | report | red    | 2020-09-01| RU           | 3
  1  | report | red    | 2020-09-01| AP           | 4
  1  | report | blue   | 2020-09-02| US           | 5   
  1  | report | blue   | 2020-09-02| EU           | 6
  1  | report | blue   | 2020-09-02| RU           | 7
  1  | report | blue   | 2020-09-02| AP           | 8
  2  | report | green  | 2020-09-02| US           | 9
  2  | report | green  | 2020-09-02| EU           | 10
  2  | report | green  | 2020-09-02| RU           | 11
  2  | report | green  | 2020-09-02| AP           | 12
  2  | report | blue   | 2020-09-03| US           | 13
  2  | report | blue   | 2020-09-03| EU           | 14
  2  | report | blue   | 2020-09-03| RU           | 15
  2  | report | blue   | 2020-09-03| AP           | 16
  </code></pre>
  
  <p>Desired output:</p>
  
  <pre><code>id | type   | color | date       | US | EU | RU | AP
  ---+--------+-------+------------+----+----+----+----
  1  | report | red   | 2020-09-01 | 1  | 2  | 3  | 4
  1  | report | blue  | 2020-09-02 | 5  | 6  | 7  | 8
  2  | report | green | 2020-09-02 | 9  | 10 | 11 | 12
  2  | report | blue  | 2020-09-03 | 13 | 14 | 15 | 16
  </code></pre>
  
  <p>Known information:</p>
  
  <ul>
  <li>there are only 4 country codes.</li>
  <li>the <code>color</code> value will be same per: id, type, date.</li>
  </ul>
  
  <p>Not sure what is the most cleanest/best way to write this SQL.</p>
  
  <p>I have tried to use </p>
  
  <pre><code>ROW_NUMBER() OVER(PARTITION BY xxx ORDER BY yyy)
  </code></pre>
  
  <p>and <code>PIVOT</code> but couldn't get the results I was looking for</p>