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
