Heirarchical data - SQL query

Creating Table:

CREATE TABLE [Category](
 [CatId] [int] IDENTITY(1,1) NOT NULL,
 [PCatId] [int] NULL,
 [CatName] [varchar](50) NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
 [CatId] ASC
))
GO

Sample Data:

Insert into [Category] ([PCatId],[CatName]) values (0,'Cat1');
Insert into [Category] ([PCatId],[CatName]) values (0,'Cat2');
Insert into [Category] ([PCatId],[CatName]) values (1,'Cat3');
Insert into [Category] ([PCatId],[CatName]) values (1,'Cat4');
Insert into [Category] ([PCatId],[CatName]) values (2,'Cat5');
Insert into [Category] ([PCatId],[CatName]) values (2,'Cat6');
Insert into [Category] ([PCatId],[CatName]) values (0,'Cat7');
Insert into [Category] ([PCatId],[CatName]) values (7,'Cat8');
Insert into [Category] ([PCatId],[CatName]) values (7,'Cat9');
Insert into [Category] ([PCatId],[CatName]) values (7,'Cat10');
Insert into [Category] ([PCatId],[CatName]) values (8,'Cat11');
Insert into [Category] ([PCatId],[CatName]) values (8,'Cat12');
Insert into [Category] ([PCatId],[CatName]) values (8,'Cat13');
GO

Final SQL Query

Now, Get Category with CatId = 8 and all it's child-hierarchy :

declare @catId int = 8;

-- select * from Category;

WITH hierarchy AS (
  SELECT c1.CatId,
         c1.CatName,
         c1.PCatId,
         CAST(NULL AS VARCHAR(50)) AS parentname
    FROM Category c1
   WHERE c1.PCatId = 0
  UNION ALL
  SELECT c2.CatId,
         c2.CatName,
         c2.PCatId,
         y.CatName
    FROM Category c2
    JOIN hierarchy y ON y.CatId = c2.PCatId)
SELECT s.CatId,
       s.CatName,
    s.PCatId,
       s.parentname
  FROM hierarchy s
  where s.PCatId = @catId OR s.CatId = @catId
  order by s.PCatId