title: All possible combinations for two column data
body: <p>I have a two column view </p>
  
  <pre><code>Product Id   Tag
  ----------------------
  1            Leather
  1            Watch
  2            Red
  2            Necklace
  2            Pearl
  </code></pre>
  
  <p>I'm trying to get all possible combinations of tags for a product as such:</p>
  
  <pre><code>1          Leather
  1          Leather,Watch
  2          Pearl
  2          Pearl,Necklace
  2          Pearl Necklace,Red
  2          Necklace
  2          Necklace, Red
  2          Red
  </code></pre>
  
  <p>I've found and stolen some SQL that give me the complete list for all but not the small versions, its below.</p>
  
  <p>Any ideas, it's started to make my head hurt.  A virtual pint for the best answer. </p>
  
  <pre><code>SELECT ProductId, 
         (SELECT CAST(Tag + ', ' AS VARCHAR(MAX)) 
            FROM ProductByTagView 
           WHERE Product.ProductId = ProductByTagView.ProductId
        order by tag
         FOR XML PATH ('')) AS Tags
  FROM Product
  </code></pre>