How to show category wise breadcrumb by sql query

select * from [Category]

CategoryID	CatName	ParentID
1	Book	NULL
2	Toy	NULL
3	fiction	1
4	HORRER	3
5	Child	2
6	Boy	5
7	Bikes, Trikes & Ride-Ons	6
8	Push Ride-Ons & Accessories	7

SELECT CategoryID, CAST(CatName AS VARCHAR(255)) as Category, 0 as ID

        FROM Category AS pm

       WHERE pm.ParentId IS NULL

CategoryID	Category	ID
1	Book	0
2	Toy	0

With Clause added by SQL in 1999 with clause to define “statement scoped views”. They are not stored in the database schema: instead, they are only valid in the query they belong to.

The with clause is also known as common table expression (CTE) and subquery factoring.

The with clause is an optional prefix for select:

WITH query_name (column_name1, ...) AS

     (SELECT ...)

    

SELECT ...

The syntax after the keyword with is the same as it is for create view: it starts with the query name, and optionally3 and in parenthesis the name of the columns it returns. The keyword as finally introduces the definition itself .

With is not a stand alone command like create view is: it must be followed by select. This query (and subqueries it contains) can refer to the just defined query name in their from clause.

A single with clause can introduce multiple query names by separating them with a comma (the with keyword is not repeated). Each of these queries can refer to the query names previously defined within the same with

WITH categories (CategoryID, CatName, ParentID)

  AS (SELECT CategoryID, CAST(CatName AS VARCHAR(255)), 0

        FROM Category AS pm

       WHERE pm.ParentId IS NULL

       UNION ALL

      SELECT ps.CategoryID, CAST(m.CatName + ' > ' + ps.CatName AS VARCHAR(255)), m.CategoryID

        FROM Category AS ps

        JOIN categories AS m

          ON m.CategoryID = ps.ParentId

       WHERE ps.ParentId IS NOT NULL

       )

SELECT * FROM categories ORDER BY CategoryID

CategoryID	CatName	ParentID
1	Book	0
2	Toy	0
3	Book > fiction	1
4	Book > fiction > HORRER	3
5	Toy > Child	2
6	Toy > Child > Boy	5
7	Toy > Child > Boy > Bikes, Trikes & Ride-Ons	6
8	Toy > Child > Boy > Bikes, Trikes & Ride-Ons > Push Ride-Ons & Accessories	7

Particular product category breadcrumb:

WITH categories (CategoryID, CatName, ParentID)

  AS (SELECT CategoryID, CAST(CatName AS VARCHAR(255)), 0

        FROM Category AS pm

       WHERE pm.ParentId IS NULL

       UNION ALL

      SELECT ps.CategoryID, CAST(m.CatName + ' > ' + ps.CatName AS VARCHAR(255)), m.CategoryID

        FROM Category AS ps

        JOIN categories AS m

          ON m.CategoryID = ps.ParentId

       WHERE ps.ParentId IS NOT NULL

       )

SELECT * FROM categories where CategoryID=8

CategoryID	CatName	ParentID
8	Toy > Child > Boy > Bikes, Trikes & Ride-Ons > Push Ride-Ons & Accessories	7

Output :  Toy > Child > Boy > Bikes, Trikes & Ride-Ons > Push Ride-Ons & Accessories