Recursive query in SQL Server

by

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

More Related Contents:

  1. select yesterday date in where condition
  2. SQL Server dynamic PIVOT query?
  3. SQL Server Process Queue Race Condition
  4. Import CSV file into SQL Server
  5. SQL Server 2005 Pivot on Unknown Number of Columns
  6. Is there a LastIndexOf in SQL Server?
  7. Get all table names of a particular database by SQL query?
  8. Turn off constraints temporarily (MS SQL)
  9. OR is not supported with CASE Statement in SQL Server
  10. SQL Server Convert Varchar to Datetime
  11. Calculating number of full months between two dates in SQL
  12. Replacing NULL with 0 in a SQL server query
  13. How do I calculate a running total in SQL without using a cursor?
  14. Equivalent of Oracle’s RowID in SQL Server
  15. Months between two dates
  16. Dynamic update statement with variable column names
  17. Why can’t I use alias in a count(*) “column” and reference it in a having clause?
  18. How to find third or nᵗʰ maximum salary from salary table?
  19. SQL “between” not inclusive
  20. Difference of two date time in sql server
  21. Benefits Of Using SQL Ordinal Position Notation?
  22. sql group by only rows which are in sequence
  23. Does UNION ALL guarantee the order of the result set [duplicate]
  24. Inserting rows into a table with one IDENTITY column only [duplicate]
  25. How to find sum of multiple columns in a table in SQL Server 2005?
  26. How to retrieve field names from temporary table (SQL Server 2008)
  27. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  28. Add a row number to result set of a SQL query
  29. Use one CTE many times
  30. How to Select Every Row Where Column Value is NOT Distinct

Leave a Comment