title: Get N most frequently bought combination product in one transaction using MSSQL
body: <p>I have a data set like below:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>transaction_id</th>
  <th>store_id</th>
  <th>product_id</th>
  <th>product_name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>100</td>
  <td>p001</td>
  <td>product_1</td>
  </tr>
  <tr>
  <td>1</td>
  <td>100</td>
  <td>p002</td>
  <td>product_2</td>
  </tr>
  <tr>
  <td>1</td>
  <td>100</td>
  <td>p003</td>
  <td>product_3</td>
  </tr>
  <tr>
  <td>4</td>
  <td>100</td>
  <td>p002</td>
  <td>product_2</td>
  </tr>
  <tr>
  <td>4</td>
  <td>100</td>
  <td>p003</td>
  <td>product_3</td>
  </tr>
  <tr>
  <td>5</td>
  <td>100</td>
  <td>p002</td>
  <td>product_2</td>
  </tr>
  <tr>
  <td>5</td>
  <td>100</td>
  <td>p003</td>
  <td>product_3</td>
  </tr>
  <tr>
  <td>7</td>
  <td>100</td>
  <td>p001</td>
  <td>product_1</td>
  </tr>
  <tr>
  <td>7</td>
  <td>100</td>
  <td>p003</td>
  <td>product_3</td>
  </tr>
  <tr>
  <td>8</td>
  <td>101</td>
  <td>p002</td>
  <td>product_2</td>
  </tr>
  <tr>
  <td>8</td>
  <td>101</td>
  <td>p003</td>
  <td>product_3</td>
  </tr>
  <tr>
  <td>9</td>
  <td>101</td>
  <td>p001</td>
  <td>product_1</td>
  </tr>
  <tr>
  <td>9</td>
  <td>101</td>
  <td>p002</td>
  <td>product_2</td>
  </tr>
  <tr>
  <td>2</td>
  <td>101</td>
  <td>p001</td>
  <td>product_1</td>
  </tr>
  <tr>
  <td>3</td>
  <td>101</td>
  <td>p002</td>
  <td>product_2</td>
  </tr>
  <tr>
  <td>3</td>
  <td>101</td>
  <td>p003</td>
  <td>product_3</td>
  </tr>
  <tr>
  <td>6</td>
  <td>101</td>
  <td>p001</td>
  <td>product_1</td>
  </tr>
  <tr>
  <td>6</td>
  <td>101</td>
  <td>p002</td>
  <td>product_2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I am trying to find a query to give output as below.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>store</th>
  <th>freq_prod_ids</th>
  <th>count_of_transactions</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>100</td>
  <td>p002, p003</td>
  <td>3</td>
  </tr>
  <tr>
  <td>100</td>
  <td>p001, p003</td>
  <td>2</td>
  </tr>
  <tr>
  <td>101</td>
  <td>p001, p002</td>
  <td>2</td>
  </tr>
  <tr>
  <td>101</td>
  <td>p002, p003</td>
  <td>2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>This essentially should give the top 2 [N=2] frequently bought product combinations in a single transaction for each store.</p>
  <p>Please help to have an SQL query to get this response.</p>