Tree structure in MSSQL 2005 Hierarchy.( Comment child lists )

Here comes another approach to build the tree structure - hierarchial flow of information. This is an simple and raw approach but effective to show the structure of hierarchy.

Let us create the database now....

GO
/****** Object:  Table [dbo].[Comments]    Script Date: 10/12/2009 14:06:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Comments](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [Type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Comment] [varchar](800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Title] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [CreatedBy] [uniqueidentifier] NULL,
   [CreateTime] [datetime] NULL,
   [ModifiedBy] [uniqueidentifier] NULL,
   [Modifiedtime] [datetime] NULL,
   [IPAddress] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [RefComment] [int] NULL,
   [Blocked] [bit] NULL,
   [Rating] [int] NULL,
   [ImageURL] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Comments_ImageURL]  DEFAULT ('a'),
CONSTRAINT [PK_Comments] 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
SET ANSI_PADDING OFF

Now Stored Procedure.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[ShowComments]
(
   @Root int
)
AS
BEGIN
   SET NOCOUNT ON
   DECLARE @ID int, @Comments varchar(2800)   
   --SET @Comments = (SELECT id+'*Comments*'+Comment+'*CreatedBy*'+CreatedBy+'*CreateTime*'+CreateTime+'*IPAddress*'+IPAddress+'*Rating*'+Rating+'*ImageURL*'+ImageURL FROM comments WHERE ID = @Root)
  
  --SET @Comments = (SELECT '<root><id>'+CAST(id as varchar(10))+'</id><comment>'+Comment+'</comment><createdby>'+CAST(CreatedBy as varchar(36))+'</createdby><createdtime>'+CAST(CreateTime as varchar(15))+'</createdtime><ip>'+CAST(IPAddress as varchar(15)) +'<ip><image>'+ ImageURL +'</image><rating>'+CAST(Rating as varchar(15))+'</rating></root>' FROM comments  WHERE ID = @Root)
SET @Comments = (SELECT '<root><id>'+CAST(id as varchar(10))+'</id><comment>'+Comment+'</comment><createdby>'+CAST(CreatedBy as varchar(36))+'</createdby><createdtime>'+Convert(nvarchar,CreateTime,103)+' '+Convert(nvarchar,CreateTime,114) +'</createdtime><ip>'+CAST(IPAddress as varchar(15)) +'<ip><image>'+ ImageURL +'</image><rating>'+CAST(Rating as varchar(15))+'</rating></root>' FROM comments  WHERE ID = @Root)
PRINT REPLICATE('-', @@NESTLEVEL * 4) + @Comments
   SET @ID = (SELECT MIN(ID) FROM comments WHERE RefComment = @Root )
   WHILE @ID IS NOT NULL
   BEGIN   
       EXEC ShowComments @ID
   SET @ID = (SELECT MIN(ID) FROM comments WHERE RefComment = @Root AND ID > @ID )
   END
END

MSSql Hierarchial- Comment like tree structure.

Approach 1:

USE [StudentInfo]
GO
/****** Object:  Table [dbo].[Testcomments]    Script Date: 10/12/2009 13:51:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Testcomments](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DeviceType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ParentId] [int] NULL,
CONSTRAINT [PK_Testcomments] 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
SET ANSI_PADDING OFF

INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (1, N'Object ', NULL)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (2, N'WebServer', 4)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (3, N'Database Server', 4)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (4, N'Server', 1)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (5, N'File Server', 4)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (6, N'Mail Server ', 4)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (7, N'IIS web server', 2)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (8, N'Virtual servers', 4)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (9, N'V S Guest OS', 8)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (10, N'VS Hosts', 8)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (11, N'New master', NULL)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (12, N'new master child', 11)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (13, N'new child1', 12)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (14, N'new child2', 13)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (15, N'new child4', 11)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (16, N'new child5', 12)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (17, N'new child6', 12)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (19, N'new child7', 13)

Stored procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- this can be used when we know the last child node of the tree

alter PROC [dbo].[TestHierar]
AS
BEGIN
 declare @ID int
 set @ID = 19;
--SET @ID = (SELECT max(ID) FROM dbo.TestComments )
with Hierarchy(ParentID,ID,level,Devicetype,Sort) As
 (

     Select ParentId,Id,0,DeviceType,cast(DeviceType as nvarchar(1024))
     from testcomments
where  Id= @ID
     union all
    select dt.ParentId, dt.ID, level+1,dt.DeviceType , cast(Sort +'|'+dt.Devicetype as nvarchar(1024))
    from TestComments dt inner join hierarchy h on dt.Id = h.parentId
)
,HierarchyReverse(ParentID,ID, level, DeviceType, Sort) as

(
select parentId, Id, 0, DeviceType, Cast(DeviceType as nvarchar(1024))
from hierarchy
where parentId is null
union all
Select h.parentId,h.id,hr.LEVEL+1, h.deviceType, cast(hr.Sort + '|' +h.sort as nvarchar(1024))
from Hierarchy h inner join HierarchyReverse hr on h.ParentId = hr.ID
)
select Id, ParentId, DeviceType, Level,Sort
from HierarchyReverse
order by sort


END

Key Points:

This approach is easy and very helpful to display the hierarchy either in treeview or any div structure using Jquery. Unfortunately it is helpful only when you have the last child id.

In stored proc for @ID you need to send the last child id.

There is an another approach of simple hierarchial structure in MSSQL 2005 in my next article.