Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.
2 Improved formatting.
source | link

create table pc ( parent varchar(10), child varchar(10) )

insert into pc values('a','b');
insert into pc values('a','c');
insert into pc values('b','e');
insert into pc values('b','f');
insert into pc values('a','d');
insert into pc values('b','g');
insert into pc values('c','h');
insert into pc values('c','i');
insert into pc values('d','j');
insert into pc values('f','k');
insert into pc values('x','y');
insert into pc values('y','z');
insert into pc values('m','n');

DECLARE @parent varchar(10) = 'a'
;WITH cte AS
(
select null parent, @parent child, 0 as level
union
SELECT a.parent, a.child , 1 as level
FROM pc a
WHERE a.parent = @parent
UNION ALL
SELECT a.parent, a.child , c.level + 1
FROM pc a JOIN cte c ON a.parent = c.child
)
SELECT distinct parent, child , level
FROM cte
order by level, parent

create table pc ( parent varchar(10), child varchar(10) )

insert into pc values('a','b');
insert into pc values('a','c');
insert into pc values('b','e');
Insert into pc values('b','f');
insert into pc values('a','d');
insert into pc values('b','g');
insert into pc values('c','h');
insert into pc values('c','i');
insert into pc values('d','j');
insert into pc values('f','k');
insert into pc values('x','y');
insert into pc values('y','z');
insert into pc values('m','n');                       

DECLARE @parent varchar(10) = 'a';
WITH cte AS
(
  select null parent, @parent child, 0 as level
   union
  SELECT  a.parent, a.child , 1 as level
    FROM pc a
   WHERE a.parent = @parent
   UNION ALL
  SELECT a.parent, a.child , c.level +    1
    FROM pc a JOIN cte c ON a.parent = c.child
)
SELECT distinct parent, child , level
  FROM cte
 order by level, parent;


  This will give you all descendants and the level.
  

Hope this helps :)

create table pc ( parent varchar(10), child varchar(10) )

insert into pc values('a','b');
insert into pc values('a','c');
insert into pc values('b','e');
insert into pc values('b','f');
insert into pc values('a','d');
insert into pc values('b','g');
insert into pc values('c','h');
insert into pc values('c','i');
insert into pc values('d','j');
insert into pc values('f','k');
insert into pc values('x','y');
insert into pc values('y','z');
insert into pc values('m','n');

DECLARE @parent varchar(10) = 'a'
;WITH cte AS
(
select null parent, @parent child, 0 as level
union
SELECT a.parent, a.child , 1 as level
FROM pc a
WHERE a.parent = @parent
UNION ALL
SELECT a.parent, a.child , c.level + 1
FROM pc a JOIN cte c ON a.parent = c.child
)
SELECT distinct parent, child , level
FROM cte
order by level, parent


  This will give you all descendants and the level.
  Hope this helps :)

create table pc ( parent varchar(10), child varchar(10) )

insert into pc values('a','b');
insert into pc values('a','c');
insert into pc values('b','e');
Insert into pc values('b','f');
insert into pc values('a','d');
insert into pc values('b','g');
insert into pc values('c','h');
insert into pc values('c','i');
insert into pc values('d','j');
insert into pc values('f','k');
insert into pc values('x','y');
insert into pc values('y','z');
insert into pc values('m','n');                       

DECLARE @parent varchar(10) = 'a';
WITH cte AS
(
  select null parent, @parent child, 0 as level
   union
  SELECT  a.parent, a.child , 1 as level
    FROM pc a
   WHERE a.parent = @parent
   UNION ALL
  SELECT a.parent, a.child , c.level +    1
    FROM pc a JOIN cte c ON a.parent = c.child
)
SELECT distinct parent, child , level
  FROM cte
 order by level, parent;

This will give you all descendants and the level. 

Hope this helps :)

1
source | link

create table pc ( parent varchar(10), child varchar(10) )

insert into pc values('a','b');
insert into pc values('a','c');
insert into pc values('b','e');
insert into pc values('b','f');
insert into pc values('a','d');
insert into pc values('b','g');
insert into pc values('c','h');
insert into pc values('c','i');
insert into pc values('d','j');
insert into pc values('f','k');
insert into pc values('x','y');
insert into pc values('y','z');
insert into pc values('m','n');

DECLARE @parent varchar(10) = 'a'
;WITH cte AS
(
select null parent, @parent child, 0 as level
union
SELECT a.parent, a.child , 1 as level
FROM pc a
WHERE a.parent = @parent
UNION ALL
SELECT a.parent, a.child , c.level + 1
FROM pc a JOIN cte c ON a.parent = c.child
)
SELECT distinct parent, child , level
FROM cte
order by level, parent


This will give you all descendants and the level.
Hope this helps :)