SQL Server Common Table Expression Hierarchical Example

   1: drop table dbo.Category

   2:  

   3: create table dbo.Category

   4: (

   5:     [Id] int Constraint PK_Category_Id primary key,

   6:     [Name] varchar(100),

   7:     [ParentId] int constraint FK_Category_ParentId references Category(Id)

   8: )

   9: GO

  10:  

  11: insert into Category([Id], [Name], [ParentId]) values(1, 'Languages', null)

  12: insert into Category([Id], [Name], [ParentId]) values(2, 'Networking', null)

  13: insert into Category([Id], [Name], [ParentId]) values(3, 'Databases', null)

  14: insert into Category([Id], [Name], [ParentId]) values(4, 'Visual Basic', 1)

  15: insert into Category([Id], [Name], [ParentId]) values(5, 'C#', 1)

  16: insert into Category([Id], [Name], [ParentId]) values(6, 'Java', 1)

  17: insert into Category([Id], [Name], [ParentId]) values(7, 'VB.Net', 4)

  18: insert into Category([Id], [Name], [ParentId]) values(8, 'VB 6.0', 4)

  19: insert into Category([Id], [Name], [ParentId]) values(9, 'Desktop Application Development with VB.Net', 7)

  20: insert into Category([Id], [Name], [ParentId]) values(10, 'Web Application Development with VB.Net', 7)

  21: insert into Category([Id], [Name], [ParentId]) values(11, 'ActiveX Objects and VB 6.0', 8)

  22: insert into Category([Id], [Name], [ParentId]) values(12, 'Network Security', 2)

  23: GO

  24:  

  25: with cteCategory

  26: as (

  27: select [Id], [Name], [ParentId]

  28: from Category

  29: where [Id] = 1

  30:  

  31: union all

  32:  

  33: select c.[Id], c.[Name], c.[ParentId] from Category as c 

  34: inner join cteCategory as p on c.[ParentId] = p.[Id]

  35: )

  36:  

  37: select [Id], [Name], [ParentId] from cteCategory

  38: GO