title: How to pivot? How to convert multiple rows into one row with multiple columns?
body: <p>I have two tables which I want to combine. The first table is with clients and the other with products. Currently I have 22 products, but I want to have a flexible DB design so instead of having 22 columns in the product DB, I have 1 row for each product for each client so if I add or remove 1 product overall, I don't have to change the DB structure. </p>
  
  <p>I want to have a select statement where I select all products for each client and the output should be in a single row with a column for each product. </p>
  
  <p>I have seen some other questions which are similar, but there the aim is to have all the rows concatenated in 1 column- which I don't want. </p>
  
  <p>Assuming 2 clients and 3 products. </p>
  
  <p>Table client:</p>
  
  <pre><code>ClientId | ClientName
  ---------------------
   1       | Name1
   2       | Name2
  </code></pre>
  
  <p>Table products</p>
  
  <pre><code>ProductId | ClientId | Product
  -------------------------------------
   1        |   1      |  SomeproductA
   2        |   1      |  SomeproductB
   3        |   1      |  SomeproductA
   4        |   2      |  SomeproductC
   5        |   2      |  SomeproductD
   6        |   2      |  SomeproductA
  </code></pre>
  
  <p>The output should be something like:</p>
  
  <p>Table output:</p>
  
  <pre><code> ClientId | ClientName | Product1     | Product 2    | Product 3
   -------------------------------------------------------------------
       1    | Name1      | SomeproductA | SomeproductB | SomeproductA
       2    | Name2      | SomeproductC | SomeproductD | SomeproductA
  </code></pre>
  
  <p>The perfect solution would also be flexible in the sense that the select statement should count the number of distinct products for each client (they will always be the same for all clients), such that if I add or remove 1 product for all clients, I should not change the select statement. </p>