Parent child record from same table

Transact-SQL
1

Hi,
Would like to get the parent child records from the same table. Sample data
and expected output given below . Tried with a self join query ,but it seems to
not work out. Any help is appreciated

DECLARE @Test TABLE
(
	catID int
	,categoryName varchar(10)
	,ParentCatID int
)

INSERT INTO @Test
VALUES
(
1
,'Fruit'
,0
),
(
2
,'Vegetable'
,0
),
(
  3
 ,'apple'
 ,1
),
(
  4
  ,'orange'
  ,1
)
,
(
  5
  ,'Carrot'
  ,2
)
,
(
  6
  ,'cabbabge'
  ,2
)

select * from @Test



    /* Expected Result */

catID    ParentCatName  ChildCatName
================================
3        Fruit            Apple
4        Fruit            Orange
5       Vegetable        Carrot
6       Vegetable        Cabbage

-- tried query
Select
ch.catID as catID
,pr.categoryName as ParentCatName
,ch.categoryName as ChildCatName

from
@Test pr
inner join @Test ch on pr.catID = ch.catID
where ch.ParentCatID = pr.catID

2 
DECLARE @Test table
    (
      catID int
    , categoryName varchar(10)
    , ParentCatID int
    );

INSERT  INTO @Test
VALUES ( 1, 'Fruit', 0 )
     , ( 2, 'Vegetable', 0 )
     , ( 3, 'Apple', 1 )
     , ( 4, 'Orange', 1 )
     , ( 5, 'Carrot', 2 )
     , ( 6, 'Cabbabge', 2 );

SELECT c.catID
     , c.categoryName Child
     , p.categoryName Parent
     , c.ParentCatID
FROM
    @Test p
JOIN @Test c 
ON p.catID = c.ParentCatID;

3

thank you

4

How many nodes are there in the hierarchy and how often is it updated? I ask because there are methods for doing this where you don't need to constantly calculate your way through the hierarchical traversal and you can do much more, as well.