One of the most benefit of CTE (Common Table Expressions) is that we can create recursive queries with them.
Recursive query is the efficient way to display hierarchy setup (Parent child relation ship in the same table). e.g. Grand Parent, Parent, Child. This is common requirement. To display the parent child relationship in efficient manner, we can use Recursive CTE. Let me explain it by example.
Create Temporary Table:
DECLARE @Images TABLE ( [ResourceID] [int] NOT NULL, [ChildOf] [int] NOT NULL, [ResourceName] [varchar](250) NOT NULL )
Lets populate some data into this table:
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (5, 0, N'Mammal') INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (6, 5, N'Carnivore') INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (14, 6, N'Cannine') INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (15, 14, N'Dog') INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (16, 15, N'Dog1') INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (17, 14, N'Monkey') INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (18, 17, N'Monkey1') INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (19, 16, N'dog1.01') INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (21, 18, N'monkey1.02') INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (23, 17, N'test') INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (25, 17, N'Monkey 2') INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (31, 0, N'Fish') INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (32, 31, N'cartilaginous fish') INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (33, 32, N'dogfish')
That is how data is stored in the table:
This is not the way that we can understand the hierarchy. Recursive query will help us to display result like this:
This is much efficient way to list out, as we understand that how the hierarchy is being maintained. Recursive CTE helps us to generate this kind efficient output. Let me share the t-sql script which generates the output which we are looking for:
;WITH images AS ( SELECT * ,ROW_NUMBER() OVER ( ORDER BY ResourceID ) AS RowID FROM @images ) ,cte AS ( SELECT 1 AS [Level] ,[ResourceID] ,[ResourceName] ,ChildOf ,cast(cast(RowID AS VARBINARY(4)) AS VARBINARY(max)) AS sort FROM images WHERE ChildOf = 0 --where [ResourceID] = 5 UNION ALL SELECT p.[Level] + 1 ,c.[ResourceID] ,c.[ResourceName] ,c.ChildOf ,cast(p.sort + cast(c.RowID AS VARBINARY(4)) AS VARBINARY(max)) FROM images c INNER JOIN cte p ON p.[ResourceID] = c.ChildOf --WHERE c.ChildOF=5 ) SELECT ResourceID ,ChildOf ,REPLICATE('.', ([Level] - 1) * 4) + [ResourceName] AS menu FROM cte ORDER BY sort
That's it. Here, I have used first CTE, to assign the Unique numbers. Then just iterate thru each record and find their related children records. Hope this helps you.
Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.
Posted by Tejas Shah on November 22, 2017
Posted by Tejas Shah on November 11, 2017
Posted by Tejas Shah on August 12, 2015
Posted by Tejas Shah on June 26, 2015
Posted by Tejas Shah on April 20, 2015