title: TSQL for getting a list of all product groups and the products in the groups
body: <p>Get a list of all product groups and the products in the groups from a MS SQL Server.
  A product can exist in multiple product groups.</p>
  <p>What i have so far, before i got lost :(</p>
  <pre><code>With CTE_Products As
  (
      Select ProductGroupNo, Name, ParentProductGroupNo
      From [dbo].[ProductGroups]
      Where ParentProductGroupNo = 0
  
      Union All
  
      Select t.ProductGroupNo, t.Name, t.ParentProductGroupNo
      From [dbo].[ProductGroups] t
      Inner Join CTE_Products c On c.ProductGroupNo = t.ParentProductGroupNo
  )
  
  Select
      ProductGroupNo, ParentProductGroupNo
  From
      CTE_Products
  ORDER BY
      ProductGroupNo
  </code></pre>
  <h1>SQL Tables</h1>
  <p>[Products] - Table with products<br>
  -ProductNo (INT)<br>
  -Description (VARCHAR)</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ProductNo</th>
  <th>Description</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Desc1</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Desc2</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Desc3</td>
  </tr>
  <tr>
  <td>4</td>
  <td>Desc4</td>
  </tr>
  <tr>
  <td>5</td>
  <td>Desc5</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>[ProductGroups] - Table with product groups, if ParentProductGroupNo is 0, the it's the root.<br>
  -ProductGroupNo (INT)<br>
  -Name (VARCHAR)<br>
  -ParentProductGroupNo (INT)</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ProductGroupNo</th>
  <th>Name</th>
  <th>ParentProductGroupNo</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Group1</td>
  <td>0</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Group2</td>
  <td>0</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Group3</td>
  <td>1</td>
  </tr>
  <tr>
  <td>4</td>
  <td>Group4</td>
  <td>1</td>
  </tr>
  <tr>
  <td>5</td>
  <td>Group5</td>
  <td>2</td>
  </tr>
  <tr>
  <td>6</td>
  <td>Group6</td>
  <td>3</td>
  </tr>
  <tr>
  <td>7</td>
  <td>Group7</td>
  <td>5</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>[Products2Groups] - Table that ties products and groups. 1 product can exist in multiple groups.<br>
  -ProductNo (INT)<br>
  -ProductGroupNo (INT)</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ProductNo</th>
  <th>ProductGroupNo</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>3</td>
  </tr>
  <tr>
  <td>2</td>
  <td>5</td>
  </tr>
  <tr>
  <td>2</td>
  <td>7</td>
  </tr>
  <tr>
  <td>3</td>
  <td>2</td>
  </tr>
  <tr>
  <td>4</td>
  <td>6</td>
  </tr>
  <tr>
  <td>4</td>
  <td>4</td>
  </tr>
  <tr>
  <td>4</td>
  <td>7</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I'm looking for a Query (probably a CTE) that can give me a resultset that looks something like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ProductNo</th>
  <th>ProductGroup</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Group1 ***** Group3</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Group2 ***** Group5</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Group2 ***** Group5 ***** Group7</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Group2</td>
  </tr>
  <tr>
  <td>4</td>
  <td>Group1 ***** Group3 ***** Group6</td>
  </tr>
  <tr>
  <td>4</td>
  <td>Group1 ***** Group4</td>
  </tr>
  <tr>
  <td>4</td>
  <td>Group2 ***** Group5 ***** Group7</td>
  </tr>
  <tr>
  <td>5</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>