3 Dec 2011

Recursive query in SQL 2008


WITH abcd
     AS (SELECT mc_compcode
                ,mc_compname
                ,mc_pcompcode
                ,CAST((mc_compname) AS VARCHAR(1000)) AS Path
         FROM   MCompany
         WHERE  mc_pcompcode IS NULL
         UNION ALL
         SELECT t.mc_compcode
                ,t.mc_compname
                ,t.mc_pcompcode
                ,CAST((a.path + ' -> ' + t.mc_compname) AS VARCHAR(1000)) AS Path
         FROM   MCompany AS t
         JOIN   abcd AS a ON t.mc_pcompcode = a.mc_compcode)
SELECT *
FROM   abcd

No comments:

Post a Comment