All possible combinations for two column data
- by Alec Dobbie
I have a two column view
Product Id Tag
----------------------
1 Leather
1 Watch
2 Red
2 Necklace
2 Pearl
I'm trying to get all possible combinations of tags for a product as such:
1 Leather
1 Leather,Watch
2 Pearl
2 Pearl,Necklace
2 Pearl Necklace,Red
2 Necklace
2 Necklace, Red
2 Red
I've found and stolen some SQL that give me the complete list for all but not the small versions, its below.
Any ideas, it's started to make my head hurt. A virtual pint for the best answer.
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
