title: Pivoting table in SQL to columns with maximum number of unique observation
body: <p>I have a sql table:</p>
  
  <pre><code>+----+--------------+-----------+
  | ID |    Genre     | Frequency |
  +----+--------------+-----------+
  |  1 | Rock         | 0.11      |
  |  1 | Punk         | 0.22      |
  |  1 | Glam         | 0.33      |
  |  2 | Metal        | 0.44      |
  |  2 | Heavy Metal  | 0.55      |
  |  2 | Thrash Metal | 0.66      |
  +----+--------------+-----------+
  </code></pre>
  
  <p>I know how pivot this to:</p>
  
  <pre><code>+----+------+------+------+-------+-------------+--------------+
  | ID | Rock | Punk | Glam | Metal | Heavy Metal | Thrash Metal |
  +----+------+------+------+-------+-------------+--------------+
  |  1 | 0.11 | 0.22 | 0.33 | NULL  | NULL        | NULL         |
  |  2 | NULL | NULL | NULL | 0.44  | 0.55        | 0.66         |
  +----+------+------+------+-------+-------------+--------------+
  </code></pre>
  
  <p>but I want table in this format:</p>
  
  <pre><code>+----+---------+-------------+-------------+-------------+--------------+-------------+
  | ID | genre 1 | frequency 1 |   genre 2   | frequency 2 |   genre 3    | frequency 3 |
  +----+---------+-------------+-------------+-------------+--------------+-------------+
  |  1 | Rock    | 0.11        | Punk        | 0.22        | Glam         | 0.33        |
  |  2 | Metal   | 0.44        | Heavy Metal | 0.55        | Thrash Metal | 0.66        |
  +----+---------+-------------+-------------+-------------+--------------+-------------+
  </code></pre>
  
  <p>This may seem weird because in most data science case, we want to have a "meaning" to the a particular column. But in this case <code>genre 1</code> does not have any "meaning". But the reason I want it in this format is because, I have a lot of artist with vary different genres. So if I have 1000 different genre and I pivot in the usual way - I will end up with 1000 different columns, one for each genre. A lot of them will be Null though.</p>
  
  <p>But, In my way of pivoting, even if I have 1000 different genre, column number will be "number of genre for the artist with highest number of genre". So, in my example, if I add another artist with 04 totally different genre, I will just have to add another column (instead of 04).</p>
  
  <p>Additionally, If I could sort by frequency within each individual artist, the variable <code>genre 1</code> will actually have meaning. It will mean "the most frequent genre". </p>
  
  <p>Please note, I am not allowed to create any new column in the main database. </p>