Hierarchy SQL with a Recursive Query, Copy Paste and Play example

Coconut SQL, Recursive Query, Tree, Hierarchy data

[coconutsql]

USE TempDB
go

-- Prepare Example Data

CREATE TABLE Inventory (name varchar(8000), id int, IDParent int)

-- Product inventory with tablets under the category Computers
Insert into dbo.Inventory(name, id, IDParent)
Values ('Ovens', 40, null),
('TV', 60, null),
('Computers', 50, null),
('Tablet', 2000, 50),
('Askus VivTab Series', 2030, 2000),
('Askus VivTab Smart 8', 2100, 2030);

SELECT * FROM dbo.inventory

/*
Hierarchy Data with a Recursive Query, Work copy-paste Example
Coconut SQL, JCejoco 2015
*/

/*
Recursive queries, in the form of Common Table Expression CTE

Have 2 elements
1) an Anchor query
2) the Recursive call

Example 1, Get category hieararchy of a

WITH T as(
-- ANCHOR
SELECT Name, ID, IDParent
FROM dbo.inventory
WHERE id=2100

UNION ALL

-- RECURSIVE
SELECT cast((I.Name + '>> ' + T.Name) as varchar(8000)) as Name, I.ID, I.IDParent
FROM dbo.inventory I inner join
T on T.idParent = I.ID
)
-- Statement using Recursive CTE
SELECT Name, ID, IDParent
FROM T
WHERE IDParent is null -- just a filter here.


/*
Example 2, Identify IDs of a tree */

WITH T as(

SELECT Name, ID, IDParent
FROM dbo.inventory
WHERE id=2100

UNION ALL

SELECT cast(( I.Name) as varchar(8000)) as Name, I.ID, I.IDParent
FROM dbo.inventory I inner join T on T.idParent = I.ID
)

SELECT id, name
FROM T