Implementing a recursive query in SQL

Question

I've a question about the use of recursive SQL in which I have following table structure

Products can be in multiple groups (for the sake of clarity, I am not using int )

CREATE TABLE ProductGroups(ProductName nvarchar(50), GroupName nvarchar(50))

INSERT INTO ProductGroups(ProductName, GroupName) values 
('Product 1', 'Group 1'),
('Product 1', 'Group 2'),
('Product 2', 'Group 1'),
('Product 2', 'Group 6'),
('Product 3', 'Group 7'),
('Product 3', 'Group 8'),
('Product 4', 'Group 6')


+-----------+---------+
|  Product  |  Group  |
+-----------+---------+
| Product 1 | Group 1 |
| Product 1 | Group 2 |
| Product 2 | Group 1 |
| Product 2 | Group 6 |
| Product 3 | Group 7 |
| Product 3 | Group 8 |
| Product 4 | Group 6 | 
+-----------+---------+

Now the Question is I want to find out all the related products so i.e. if I pass Product 1 then I need the following result

+-----------+---------+
|  Product  |  Group  |
+-----------+---------+
| Product 1 | Group 1 |
| Product 1 | Group 2 |
| Product 2 | Group 1 |
| Product 2 | Group 6 |    
| Product 4 | Group 6 | 
+-----------+---------+

So basically I want to first find out all the Groups for product 1 and then for each group I want to find out all the products and so on...

1. Product 1 => Group 1, Group 2;
2. Group 1 => Product 1, Product 2 (Group 1 and Product 1 already exist so should be avoided otherwise would go into infinite loop);
3. Group 2 => Product 1 (already exist so same as above);
4. Product 2 => Group 1, Group 6 (Group 1 and Product 2 already exist)
5. Group 6 => Product 4

Answer 1

It can be done with a recursive query, but it's not optimal because SQL Server does not allow you to reference the recursive table as a set. So you end up having to keep a path string to avoid infinite loops. If you use ints you can replace the path string with a hierarchyid.

with r as (
    select ProductName Root, ProductName, GroupName, convert(varchar(max), '/') Path from ProductGroups
    union all
    select r.Root, pg.ProductName, pg.GroupName, convert(varchar(max), r.Path + r.ProductName + ':' + r.GroupName + '/')
    from r join ProductGroups pg on pg.GroupName=r.GroupName or pg.ProductName=r.ProductName
    where r.Path not like '%' + pg.ProductName + ':' + pg.GroupName + '%'
)

select distinct ProductName, GroupName from r where Root='Product 1'

http://sqlfiddle.com/#!3/a65d1/5/0