Recursive query in SQL Server

sql, sql-server

Solution

Try this:

;WITH CTE
AS
(
    SELECT DISTINCT
        M1.Product_ID Group_ID,
        M1.Product_ID
    FROM matches M1
        LEFT JOIN matches M2
            ON M1.Product_Id = M2.matching_Product_Id
    WHERE M2.matching_Product_Id IS NULL
    UNION ALL
    SELECT
        C.Group_ID,
        M.matching_Product_Id
    FROM CTE C
        JOIN matches M
            ON C.Product_ID = M.Product_ID
)
SELECT * FROM CTE ORDER BY Group_ID

You can use `OPTION(MAXRECURSION n)` to control recursion depth.

SQL FIDDLE DEMO

Problem

I have a table with following structure Table name: `matches` That basically stores which product is matching which product. I need to process this table And store in a groups table like below. Table Name: `groups` `group_ID` stores the `MIN Product_ID` of the `Product_IDS` that form a group. To give an example let's say If A is matching B and B is Matching C then three rows should go to group table in format `(A, A), (A, B), (A, C)` I have tried looking into co-related subqueries and CTE, but not getting this to implement. I need to do this all in SQL. Thanks for the help .

Original source