Cacher is the code snippet organizer for pro developers

SET NOCOUNT ON;

/*
  Given a tree:

        A
       / \
      /   \
     /     \ 
    B       C
   / \     / \
  D   E   F   G

*/


DECLARE @nodes TABLE
(
   id        varchar(1) NOT NULL PRIMARY KEY CLUSTERED
  ,parent_id varchar(1)     NULL
);

INSERT INTO @nodes (id, parent_id)
VALUES
  ('A', NULL)
 ,('B', 'A')
 ,('C', 'A')
 ,('D', 'B')
 ,('E', 'B')
 ,('F', 'C')
 ,('G', 'C');
 
PRINT 'Tree:';
SELECT parent_id, id
FROM @nodes
ORDER BY parent_id, id;

/*
  Tree:
  parent_id id
  --------- ----
  NULL      A
  A         B
  A         C
  B         D
  B         E
  C         F
  C         G
*/


PRINT 'Root:';
SELECT id
FROM @nodes
WHERE parent_id IS NULL;

/*
  Root:
  id
  ----
  A
*/


PRINT 'Leaf nodes:';
SELECT id
FROM @nodes n
WHERE NOT EXISTS (SELECT * FROM @nodes c WHERE c.parent_id = n.id)
ORDER BY id;

/*
  Leaf nodes:
  id
  ----
  D
  E
  F
  G
*/


PRINT 'Note: Descendents of a node always include the node itself';
PRINT 'Each node with all of its descendents:';
WITH recursive_cte(parent_id, id) AS
(
   SELECT id AS parent_id, id
     FROM @nodes
   UNION ALL
   SELECT r.parent_id, n.id
     FROM @nodes n
     INNER JOIN recursive_cte r ON r.id = n.parent_id
)
SELECT parent_id, id
FROM recursive_cte
ORDER BY parent_id, id;

/*
  Note: Descendents of a node always include the node itself
  Each node with all of its descendents:
  parent_id id
  --------- ----
  A         A
  A         B
  A         C
  A         D
  A         E
  A         F
  A         G
  B         B
  B         D
  B         E
  C         C
  C         F
  C         G
  D         D
  E         E
  F         F
  G         G
*/


PRINT 'Note: Descendents of a node always include the node itself';
PRINT 'Each node with all descendent leaf nodes:';
WITH recursive_cte(parent_id, id) AS
(
   SELECT id AS parent_id, id
     FROM @nodes
   UNION ALL
   SELECT r.parent_id, n.id
     FROM @nodes n
     INNER JOIN recursive_cte r ON r.id = n.parent_id
)
SELECT parent_id, id
FROM recursive_cte n
WHERE NOT EXISTS (SELECT * FROM @nodes c WHERE c.parent_id = n.id)
ORDER BY parent_id, id;

/*
  Note: Descendents of a node always include the node itself
  Each node with all descendent leaf nodes:
  parent_id id
  --------- ----
  A         D
  A         E
  A         F
  A         G
  B         D
  B         E
  C         F
  C         G
  D         D
  E         E
  F         F
  G         G
*/