0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and amateur woodworkers

DECLARE @myTable TABLE
       (childID INT ,parentID INT ,NAME NVARCHAR(30),location int);

INSERT INTO @myTable
SELECT 1, 1, 'a',1
UNION ALL
SELECT 2, 1, 'b',1
UNION ALL
SELECT 3, 2, 'c',2
UNION ALL
SELECT 5, 1, 'e',2
UNION ALL
SELECT 6, 6, 'f',2
UNION ALL
SELECT 7, 6, 'r1',0
UNION ALL
SELECT 4, 2, 'd',1

 
 ;
WITH CTE AS
(
   SELECT  childID
         ,parentID,
         0 AS depth,NAME , location
   FROM   @myTable
   WHERE   childID = parentID 
    
   
    UNION ALL
    
    SELECT  top 100 percent  TBL.childID
          ,TBL.parentID,
           CTE.depth + 1 , TBL.name ,TBL.location
    FROM   @myTable AS TBL
            INNER JOIN CTE  ON  TBL.parentID = CTE.childID
    WHERE   TBL.childID<>TBL.parentID  
  
)
SELECT depth,childID,parentID,location,REPLICATE('----', depth) + name
     
FROM   CTE   
 ORDER BY    childid

fork query

edited jul 16 anonymous

created aug 3 13 RoyiNamir - SqlOnline