First of all create to store hierarchical data in sql database
- CREATE TABLE [dbo].[TBL_Tree](
- [Id] [int] IDENTITY(1,1) NOT NULL,
- [ParentId] [int] NULL,
- [Name] [varchar](50) NULL,
- [IsDisplay] [bit] NULL,
- CONSTRAINT [PK_TBL_Tree] PRIMARY KEY CLUSTERED
- (
- [Id] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
Now add below records in table
- SET IDENTITY_INSERT [dbo].[TBL_Tree] ON
- GO
- INSERT [dbo].[TBL_Tree] ([Id], [ParentId], [Name], [IsDisplay]) VALUES (1, NULL, N'A', NULL)
- GO
- INSERT [dbo].[TBL_Tree] ([Id], [ParentId], [Name], [IsDisplay]) VALUES (2, NULL, N'B', NULL)
- GO
- INSERT [dbo].[TBL_Tree] ([Id], [ParentId], [Name], [IsDisplay]) VALUES (3, 1, N'A1', NULL)
- GO
- INSERT [dbo].[TBL_Tree] ([Id], [ParentId], [Name], [IsDisplay]) VALUES (4, 1, N'A2', NULL)
- GO
- INSERT [dbo].[TBL_Tree] ([Id], [ParentId], [Name], [IsDisplay]) VALUES (5, 1, N'A3', NULL)
- GO
- INSERT [dbo].[TBL_Tree] ([Id], [ParentId], [Name], [IsDisplay]) VALUES (6, 2, N'B1', NULL)
- GO
- INSERT [dbo].[TBL_Tree] ([Id], [ParentId], [Name], [IsDisplay]) VALUES (7, 2, N'B2', NULL)
- GO
- INSERT [dbo].[TBL_Tree] ([Id], [ParentId], [Name], [IsDisplay]) VALUES (8, 3, N'A11', NULL)
- GO
- INSERT [dbo].[TBL_Tree] ([Id], [ParentId], [Name], [IsDisplay]) VALUES (9, 3, N'A12', NULL)
- GO
- INSERT [dbo].[TBL_Tree] ([Id], [ParentId], [Name], [IsDisplay]) VALUES (10, 7, N'B21', NULL)
- GO
- INSERT [dbo].[TBL_Tree] ([Id], [ParentId], [Name], [IsDisplay]) VALUES (11, 7, N'B22', NULL)
- GO
- INSERT [dbo].[TBL_Tree] ([Id], [ParentId], [Name], [IsDisplay]) VALUES (12, 9, N'A121', NULL)
- GO
- INSERT [dbo].[TBL_Tree] ([Id], [ParentId], [Name], [IsDisplay]) VALUES (13, 9, N'A122', NULL)
- GO
- SET IDENTITY_INSERT [dbo].[TBL_Tree] OFF
- GO
Now retrieve hierarchical data from sql using CTE
- With ItemGroup as
- (
- Select BI.Id,Bi.ParentId,Bi.Name, 1 as IGLevel From TBL_Tree BI where ISNULL(BI.ParentId,0)=0
- Union All
- Select BIN.Id,BiN.ParentId,BiN.Name, IG.IGLevel +1 From TBL_Tree BIN
- Inner Join ItemGroup IG on (BIN.ParentId = IG.Id )
- )
- Select * From ItemGroup order by IGLevel, ParentId