title: Python sqlite3: Pivot a Column and create a view based upon the pivoted data
body: <p>New to sqlite3 database. Trying to retrieve the data from the table and pivot the data from Column and create a table view based upon that. I looked around and some suggested to use CASE and some suggested to use joins but i am not able to figure out how to write the sql statement to do the pivot on col2 in the table below. Col2 can have n number of rows but need to take only the unique values from col2 and use those unique values to pivot the table. Not sure if this is possible using sqlite3 or should I be doing this outside sqlite3 and use pandas?</p>
  <p>Input data is as follows:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">col1</th>
  <th style="text-align: center;">col2</th>
  <th style="text-align: center;">col3</th>
  <th style="text-align: center;">col4</th>
  <th style="text-align: center;">col5</th>
  <th style="text-align: center;">col6</th>
  <th style="text-align: center;">col7</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">test1</td>
  <td style="text-align: center;">t1</td>
  <td style="text-align: center;">Dummy1</td>
  <td style="text-align: center;">result1</td>
  <td style="text-align: center;">10</td>
  <td style="text-align: center;">100.2</td>
  </tr>
  <tr>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">test1</td>
  <td style="text-align: center;">t1</td>
  <td style="text-align: center;">Dummy2</td>
  <td style="text-align: center;">result2</td>
  <td style="text-align: center;">20</td>
  <td style="text-align: center;">101.2</td>
  </tr>
  <tr>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">test1</td>
  <td style="text-align: center;">t1</td>
  <td style="text-align: center;">Dummy3</td>
  <td style="text-align: center;">result3</td>
  <td style="text-align: center;">30</td>
  <td style="text-align: center;">102.3</td>
  </tr>
  <tr>
  <td style="text-align: center;">4</td>
  <td style="text-align: center;">test1</td>
  <td style="text-align: center;">t1</td>
  <td style="text-align: center;">Dummy4</td>
  <td style="text-align: center;">result4</td>
  <td style="text-align: center;">40</td>
  <td style="text-align: center;">101.4</td>
  </tr>
  <tr>
  <td style="text-align: center;">5</td>
  <td style="text-align: center;">test2</td>
  <td style="text-align: center;">t1</td>
  <td style="text-align: center;">Dummy1</td>
  <td style="text-align: center;">result1</td>
  <td style="text-align: center;">10</td>
  <td style="text-align: center;">101</td>
  </tr>
  <tr>
  <td style="text-align: center;">6</td>
  <td style="text-align: center;">test2</td>
  <td style="text-align: center;">t1</td>
  <td style="text-align: center;">Dummy2</td>
  <td style="text-align: center;">result2</td>
  <td style="text-align: center;">20</td>
  <td style="text-align: center;">103</td>
  </tr>
  <tr>
  <td style="text-align: center;">7</td>
  <td style="text-align: center;">test2</td>
  <td style="text-align: center;">t1</td>
  <td style="text-align: center;">Dummy3</td>
  <td style="text-align: center;">result3</td>
  <td style="text-align: center;">30</td>
  <td style="text-align: center;">104</td>
  </tr>
  <tr>
  <td style="text-align: center;">8</td>
  <td style="text-align: center;">test2</td>
  <td style="text-align: center;">t1</td>
  <td style="text-align: center;">Dummy4</td>
  <td style="text-align: center;">result4</td>
  <td style="text-align: center;">40</td>
  <td style="text-align: center;">105</td>
  </tr>
  <tr>
  <td style="text-align: center;">9</td>
  <td style="text-align: center;">test3</td>
  <td style="text-align: center;">t1</td>
  <td style="text-align: center;">Dummy1</td>
  <td style="text-align: center;">result1</td>
  <td style="text-align: center;">10</td>
  <td style="text-align: center;">102</td>
  </tr>
  <tr>
  <td style="text-align: center;">10</td>
  <td style="text-align: center;">test3</td>
  <td style="text-align: center;">t1</td>
  <td style="text-align: center;">Dummy2</td>
  <td style="text-align: center;">result2</td>
  <td style="text-align: center;">20</td>
  <td style="text-align: center;">106</td>
  </tr>
  <tr>
  <td style="text-align: center;">11</td>
  <td style="text-align: center;">test3</td>
  <td style="text-align: center;">t1</td>
  <td style="text-align: center;">Dummy3</td>
  <td style="text-align: center;">result3</td>
  <td style="text-align: center;">30</td>
  <td style="text-align: center;">107</td>
  </tr>
  <tr>
  <td style="text-align: center;">12</td>
  <td style="text-align: center;">test3</td>
  <td style="text-align: center;">t1</td>
  <td style="text-align: center;">Dummy5</td>
  <td style="text-align: center;">result4</td>
  <td style="text-align: center;">50</td>
  <td style="text-align: center;">110.2</td>
  </tr>
  <tr>
  <td style="text-align: center;">13</td>
  <td style="text-align: center;">test4</td>
  <td style="text-align: center;">t1</td>
  <td style="text-align: center;">Dummy2</td>
  <td style="text-align: center;">result2</td>
  <td style="text-align: center;">20</td>
  <td style="text-align: center;">120</td>
  </tr>
  <tr>
  <td style="text-align: center;">14</td>
  <td style="text-align: center;">test5</td>
  <td style="text-align: center;">t1</td>
  <td style="text-align: center;">Dummy6</td>
  <td style="text-align: center;">result1</td>
  <td style="text-align: center;">100</td>
  <td style="text-align: center;">88</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Output should look like below:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">col4</th>
  <th style="text-align: center;">col5</th>
  <th style="text-align: center;">col6</th>
  <th style="text-align: center;">test1</th>
  <th style="text-align: center;">test2</th>
  <th style="text-align: center;">test3</th>
  <th style="text-align: center;">test4</th>
  <th style="text-align: center;">test5</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">Dummy1</td>
  <td style="text-align: center;">result1</td>
  <td style="text-align: center;">10</td>
  <td style="text-align: center;">100.2</td>
  <td style="text-align: center;">101</td>
  <td style="text-align: center;">102</td>
  <td style="text-align: center;"></td>
  <td style="text-align: center;"></td>
  </tr>
  <tr>
  <td style="text-align: center;">Dummy6</td>
  <td style="text-align: center;">result1</td>
  <td style="text-align: center;">100</td>
  <td style="text-align: center;"></td>
  <td style="text-align: center;"></td>
  <td style="text-align: center;"></td>
  <td style="text-align: center;"></td>
  <td style="text-align: center;">88</td>
  </tr>
  <tr>
  <td style="text-align: center;">Dummy2</td>
  <td style="text-align: center;">result2</td>
  <td style="text-align: center;">20</td>
  <td style="text-align: center;">101.2</td>
  <td style="text-align: center;">103</td>
  <td style="text-align: center;">106</td>
  <td style="text-align: center;">120</td>
  <td style="text-align: center;"></td>
  </tr>
  <tr>
  <td style="text-align: center;">Dummy3</td>
  <td style="text-align: center;">result3</td>
  <td style="text-align: center;">30</td>
  <td style="text-align: center;">102.3</td>
  <td style="text-align: center;">104</td>
  <td style="text-align: center;">107</td>
  <td style="text-align: center;"></td>
  <td style="text-align: center;"></td>
  </tr>
  <tr>
  <td style="text-align: center;">Dummy4</td>
  <td style="text-align: center;">result4</td>
  <td style="text-align: center;">40</td>
  <td style="text-align: center;">101.4</td>
  <td style="text-align: center;">105</td>
  <td style="text-align: center;"></td>
  <td style="text-align: center;"></td>
  <td style="text-align: center;"></td>
  </tr>
  <tr>
  <td style="text-align: center;">Dummy5</td>
  <td style="text-align: center;">result4</td>
  <td style="text-align: center;">50</td>
  <td style="text-align: center;"></td>
  <td style="text-align: center;"></td>
  <td style="text-align: center;"></td>
  <td style="text-align: center;"></td>
  <td style="text-align: center;"></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Any guidance is much appreciated.</p>