Recursive query in SQL Server

Question

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

User · Answer

Sample of the Recursive Level     DECLARE  VALUE CODE AS VARCHAR 5    --SET  VALUE CODE    A  -- Specify a level  WITH ViewValue AS       SELECT ValueCode       ValueDesc       PrecedingValueCode     FROM ValuesTable     WHERE PrecedingValueCode IS NULL     UNION ALL     SELECT A ValueCode       A ValueDesc       A PrecedingValueCode      FROM ValuesTable A     INNER JOIN ViewValue V ON         V ValueCode   A PrecedingValueCode    SELECT ValueCode  ValueDesc  PrecedingValueCode  FROM ViewValue  --WHERE PrecedingValueCode     VALUE CODE -- Specific level  --WHERE PrecedingValueCode  IS NULL -- Root

User · Answer

Something like this  not tested   with match groups as      select product id            matching product id           product id as group id   from matches   where product id not in  select matching product id from matches     union all    select m product id  m matching product id  p group id   from matches m     join match groups p on m product id   p matching product id   select group id  product id from match groups order by group id

User · Answer

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

[sql] Recursive query in SQL Server

Examples related to sql

Examples related to sql-server