CODEWITHSUNDEEP
sqlt-sql
Simant
Simant

Reputation: 4350

Category and Subcategory display (Parent/Child)

I have a table which contains parent Sports whose Parent ID is always 0 and child Sports which have ParentID as SportID of the Parent Sport. Few sample records are given below.

SportID SportName ParentID 1 Cricket 0 2 T20 Cricket 1 3 50 Over Cricket 1 4 Test Cricket 1 5 Football 0 6 World cup football 5 7 Euro Cup Football 5 8 Volleyball 0 9 Beach Volleyball 8 10 Ground Volleyball 8

I need to write a Stored procedure so that the output should be like the     following.

SportID SportName 1 Cricket 2 - T20 Cricket 3 - 50 Over Cricket 4 - Test Cricket 5 Football 6 -World cup football 7 - Euro cup Football 8 Volleyball 9 - Beach Volleyball 10 - Ground Volleyball

Note: The records are in random order in my table. I really need someone's help to solve the problem.

Upvotes: 0

Views: 48

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

A recursive CTE can do this - down to any depth:

EDIT: added proper sorting and added one element third level "Test Cricket2" below ID 4

DECLARE @tbl TABLE(SportID INT,SportName VARCHAR(100),ParentID INT);
INSERT INTO @tbl VALUES
 (1,'Cricket',0)
,(2,'T20 Cricket',1)
,(3,'50 Over Cricket',1)
,(4,'Test Cricket',1)
,(5,'Football',0)
,(6,'World cup football',5)
,(7,'Euro Cup Football',5)
,(11,'Test Cricket2',4)
,(8,'Volleyball',0)
,(9,'Beach Volleyball',8)
,(10,'Ground Volleyball',8);

WITH RecursiveCTE AS
(
    SELECT CAST(ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS VARCHAR(MAX)) AS Inx, SportID,SportName,ParentID,1 AS Level
    FROM @tbl 
    WHERE ParentID=0
    UNION ALL
    SELECT rc.Inx + '.' +CAST(ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS VARCHAR(10)),tbl.SportID,CAST(REPLICATE('--',rc.Level) + tbl.SportName AS VARCHAR(100)),tbl.ParentID,rc.Level +1 
    FROM RecursiveCTE AS rc
    INNER JOIN @tbl AS tbl ON tbl.ParentID=rc.SportID
)
SELECT * FROM RecursiveCTE
ORDER BY Inx,Level

The result

+-------+---------+----------------------+----------+-------+
| Inx   | SportID | SportName            | ParentID | Level |
+-------+---------+----------------------+----------+-------+
| 1     | 1       | Cricket              | 0        | 1     |
+-------+---------+----------------------+----------+-------+
| 1.1   | 2       | --T20 Cricket        | 1        | 2     |
+-------+---------+----------------------+----------+-------+
| 1.2   | 3       | --50 Over Cricket    | 1        | 2     |
+-------+---------+----------------------+----------+-------+
| 1.3   | 4       | --Test Cricket       | 1        | 2     |
+-------+---------+----------------------+----------+-------+
| 1.3.1 | 11      | ----Test Cricket2    | 4        | 3     |
+-------+---------+----------------------+----------+-------+
| 2     | 5       | Football             | 0        | 1     |
+-------+---------+----------------------+----------+-------+
| 2.1   | 6       | --World cup football | 5        | 2     |
+-------+---------+----------------------+----------+-------+
| 2.2   | 7       | --Euro Cup Football  | 5        | 2     |
+-------+---------+----------------------+----------+-------+
| 3     | 8       | Volleyball           | 0        | 1     |
+-------+---------+----------------------+----------+-------+
| 3.1   | 9       | --Beach Volleyball   | 8        | 2     |
+-------+---------+----------------------+----------+-------+
| 3.2   | 10      | --Ground Volleyball  | 8        | 2     |
+-------+---------+----------------------+----------+-------+

Upvotes: 1

Related Questions