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