SQL Get All Categories And All Sub Categories

Question

Can you help me to get all categories with all sub categories like that ?

Database Creation Script

CREATE TABLE Category(Id INT NOT NULL, ParentId INT NULL)
GO
INSERT INTO Category (Id, ParentId) VALUES (1, NULL)
INSERT INTO Category (Id, ParentId) VALUES (2, 1)
INSERT INTO Category (Id, ParentId) VALUES (3, 1)
INSERT INTO Category (Id, ParentId) VALUES (4, 2)
INSERT INTO Category (Id, ParentId) VALUES (5, 2)
INSERT INTO Category (Id, ParentId) VALUES (6, 2)
GO

I tried in ms-sql to solve that with using cursor technique and join method but can get all data.

SELECT parent.Id AS ParentId, parent.ParentId AS ChildId FROM Category parent
JOIN Category child ON parent.Id = child.Id

Expecting Result

ParentId    ChildId
----------- -----------
1           1
1           2
1           3
1           4
1           5
1           6
2           2
2           4
2           5
2           6
3           3
4           4
5           5
6           6

Thank You

Could you post your code and show what you have tried? Moreover, could you tag which RDMS you are using, oracle, sql-server or others. — Prisoner, Aug 04 '17 at 08:30
Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — Daria Pydorenko, Aug 04 '17 at 08:32
Which [DBMS](https://en.wikipedia.org/wiki/Database) are you using? Postgres? Oracle? DB2? Firebird? — , Aug 04 '17 at 08:38
https://stackoverflow.com/questions/tagged/sql+recursive-query — , Aug 04 '17 at 08:39
I'm tring to solve in ms sql and yes it it recursive. But 1 "category one" shound have 1,2,3,4,5,6 childs in my senario, thanks. — Saygın Karahan, Aug 04 '17 at 08:47
It is not duplicate of your link @DariaPydorenko, my expecting output is different. For Expample, one should have 6 childs in my case, thanks. — Saygın Karahan, Aug 04 '17 at 09:07
Is it possible to alter the database schema? such as adding two extra columns with pre-computed values? — Radim Bača, Aug 04 '17 at 11:30

Nenad Zivkovic · Accepted Answer · 2017-08-04T10:13:50.993

4

WITH RCTE AS 
(
    SELECT * , Id AS TopLevelParent
    FROM dbo.Category c

    UNION ALL

    SELECT c.* , r.TopLevelParent
    FROM dbo.Category c
    INNER JOIN RCTE r ON c.ParentId = r.Id
)
SELECT 
  r.TopLevelParent AS ParentID
, r.Id AS ChildID 
FROM RCTE r
ORDER BY ParentID;

SQLFiddle DEMO

edited Aug 04 '17 at 10:13

answered Aug 04 '17 at 10:07

Nenad Zivkovic

18,221
6
42
55

Thank you sir, it returns expected output result. – Saygın Karahan Aug 04 '17 at 11:42

score 1 · Answer 2 · answered Aug 04 '17 at 08:53

As has been pointed out, you need a recursive CTE for this. One thing that makes this one a bit trickier is the fact you essentially are transforming ID (int) into character data (-).

I believe the below query will get you started for the desired results. This does not take into account that you want to essentially list all children, regardless of their spot in the hierarchy, though.

WITH CategoryTree AS
(
    SELECT 
        ID AS CategoryID
        , CAST(ID AS varchar(50)) AS CatVar
        , 0 AS LeafLevel    
        , ParentID
    FROM Category WHERE ParentID IS NULL

    UNION ALL

    SELECT 
        ID AS CategoryID 
        , CAST(CONCAT(CAST(Category.ParentID AS varchar), '-', CAST(Id AS varchar)) AS varchar(50)) AS CatVar
        , CategoryTree.LeafLevel + 1 AS LeafLevel   
        , Category.ParentID
    FROM Category 
        JOIN CategoryTree ON Category.ParentId = CategoryTree.CategoryId
    WHERE Category.ParentID IS NOT NULL
)
SELECT * FROM CategoryTree

Output:

CategoryID  CatVar  LeafLevel   ParentID
1           1       0           NULL
2           1-2     1           1
3           1-3     1           1
4           2-4     2           2
5           2-5     2           2
6           2-6     2           2

Hello, your result is not same as my expecting result output but thank you for your help. — Saygın Karahan, Aug 04 '17 at 09:04
@SaygınKarahan - you can try changing his query around to see how it affects the results. It will also help you understand how it works. Hint: try removing `WHERE ParentID IS NULL` from `FROM Category WHERE ParentID IS NULL` — Alex, Aug 04 '17 at 09:32
@SaygınKarahan - you have tagged the question SQL Server 2008 - but your comment infers this runs fine for you; this answer uses the CONCAT function which wasn't introduced until SQL Server 2012. Which version are you actually running? — Bridge, Aug 04 '17 at 10:13

SQL Get All Categories And All Sub Categories

2 Answers2