How to show a hierarchical row, e.g. parent, using SQL query

Question

I use blog comments in my application, where I insert each line of comments into a single table, so if any of them responds to a specific comment that commentid clicked, I insert as replyid into a new line.

Below is a screenshot:

Here you can see commentid 24 and 26 with replycommentid 23. I need a request to show 24 and 26 immediately after 23. Since 23 is the parent of 24 and 26.

The following is a script setup for placing tables and sample data:

 USE [myDB] GO /****** Object: Table [dbo].[Blog_CommentDetails] Script Date: 11/12/2016 6:36:04 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Blog_CommentDetails]( [CommentID] [int] IDENTITY(1,1) NOT NULL, [CommentUserName] [nvarchar](200) NOT NULL, [CommentText] [nvarchar](max) NULL, [CommentApprovedByUserID] [int] NULL, [CommentPostDocumentID] [int] NOT NULL, [CommentDate] [datetime] NULL DEFAULT (getdate()), [HtmlComment] [nvarchar](max) NULL, [CommentIsSpam] [bit] NULL CONSTRAINT [DEFAULT_Blog_MainComment_CommentIsSpam] DEFAULT ((0)), [CommentIsApproved] [bit] NULL CONSTRAINT [DEFAULT_Blog_MainComment_CommentIsApproved] DEFAULT ((0)), [CommentEmail] [nvarchar](250) NULL, [CommentInfo] [nvarchar](max) NULL, [ReplyCommentID] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[Blog_CommentDetails] ON GO INSERT [dbo].[Blog_CommentDetails] ([CommentID], [CommentUserName], [CommentText], [CommentApprovedByUserID], [CommentPostDocumentID], [CommentDate], [HtmlComment], [CommentIsSpam], [CommentIsApproved], [CommentEmail], [CommentInfo], [ReplyCommentID]) VALUES (22, N'Vikash', N'This is main comment', NULL, 1, CAST(N'2016-11-12 17:36:25.637' AS DateTime), N'<div class="main-comment-section"><div class="row"><div class="col-xs-12"><div class="post-comment-section"><p id="pCommentorName"><span id="sCommentorName" class="blogcommentname">Vikash</span><span id="sBlogPostedDate">Nov12,2016 5:35PM</span></p><p class="comment-detail" id="pBlogCommentDetails">This is main comment</p><div class="comment-reply"><ul><li><img src="assets/svg/components/blog-detail/icon_reply.svg" alt="Reply comment icon"><a class="joinus-link" id="btnComment" href="#" target="_self">::blogdetailsReply</a></li></ul></div></div></div></div></div>', 0, 1, N' [e-mail] ', NULL, NULL) GO INSERT [dbo].[Blog_CommentDetails] ([CommentID], [CommentUserName], [CommentText], [CommentApprovedByUserID], [CommentPostDocumentID], [CommentDate], [HtmlComment], [CommentIsSpam], [CommentIsApproved], [CommentEmail], [CommentInfo], [ReplyCommentID]) VALUES (23, N'Megha k', N'This is reply comment', NULL, 1, CAST(N'2016-11-12 17:39:04.250' AS DateTime), N'<div class="reply-comment-section"><div class="row"><div class="col-xs-12"><div class="post-comment-section"><p id="pBlogReplyCommentorName"><span id="sReplyCommentorName" class="blogcommentname">Megha k</span><span id="sBlogReplyCommentDate">Nov12,2016 5:38PM</span></p><p class="comment-detail" id="pBlogReplyCommentDetails">This is reply comment</p><div class="comment-reply"><ul><li><img class="contributors-list" src="assets/svg/components/blog-detail/icon_reply.svg" alt="Reply comment icon"><a class="joinus-link" id="btnCommentReply" href="#" target="_self">::blogdetailsReply</a></li></ul></div></div></div></div></div>', 0, 1, N' [e-mail] ', NULL, NULL) GO INSERT [dbo].[Blog_CommentDetails] ([CommentID], [CommentUserName], [CommentText], [CommentApprovedByUserID], [CommentPostDocumentID], [CommentDate], [HtmlComment], [CommentIsSpam], [CommentIsApproved], [CommentEmail], [CommentInfo], [ReplyCommentID]) VALUES (24, N'Siddappa H', N'This is reply text.', NULL, 1, CAST(N'2016-11-12 17:39:58.847' AS DateTime), N'<div class="main-comment-section"><div class="row"><div class="col-xs-12"><div class="post-comment-section"><p id="pCommentorName"><span id="sCommentorName" class="blogcommentname">Siddappa H</span><span id="sBlogPostedDate">Nov12,2016 5:39PM</span></p><p class="comment-detail" id="pBlogCommentDetails">This is reply text.</p><div class="comment-reply"><ul><li><img src="assets/svg/components/blog-detail/icon_reply.svg" alt="Reply comment icon"><a class="joinus-link" id="btnComment" href="#" target="_self">::blogdetailsReply</a></li></ul></div></div></div></div></div>', 0, 1, N' [e-mail] ', NULL, 23) GO INSERT [dbo].[Blog_CommentDetails] ([CommentID], [CommentUserName], [CommentText], [CommentApprovedByUserID], [CommentPostDocumentID], [CommentDate], [HtmlComment], [CommentIsSpam], [CommentIsApproved], [CommentEmail], [CommentInfo], [ReplyCommentID]) VALUES (25, N'Suresh P', N'This is reply comment', NULL, 1, CAST(N'2016-11-12 17:40:44.470' AS DateTime), N'<div class="reply-comment-section"><div class="row"><div class="col-xs-12"><div class="post-comment-section"><p id="pBlogReplyCommentorName"><span id="sReplyCommentorName" class="blogcommentname">Suresh P</span><span id="sBlogReplyCommentDate">Nov12,2016 5:40PM</span></p><p class="comment-detail" id="pBlogReplyCommentDetails">This is reply comment</p><div class="comment-reply"><ul><li><img class="contributors-list" src="assets/svg/components/blog-detail/icon_reply.svg" alt="Reply comment icon"><a class="joinus-link" id="btnCommentReply" href="#" target="_self">::blogdetailsReply</a></li></ul></div></div></div></div></div>', 0, 1, N' [e-mail] ', NULL, NULL) GO INSERT [dbo].[Blog_CommentDetails] ([CommentID], [CommentUserName], [CommentText], [CommentApprovedByUserID], [CommentPostDocumentID], [CommentDate], [HtmlComment], [CommentIsSpam], [CommentIsApproved], [CommentEmail], [CommentInfo], [ReplyCommentID]) VALUES (26, N'Vikash', N'This is reply text', NULL, 1, CAST(N'2016-11-12 17:41:44.673' AS DateTime), N'<div class="reply-comment-section"><div class="row"><div class="col-xs-12"><div class="post-comment-section"><p id="pBlogReplyCommentorName"><span id="sReplyCommentorName" class="blogcommentname">Vikash</span><span id="sBlogReplyCommentDate">Nov12,2016 5:40PM</span></p><p class="comment-detail" id="pBlogReplyCommentDetails">This is reply text</p><div class="comment-reply"><ul><li><img class="contributors-list" src="assets/svg/components/blog-detail/icon_reply.svg" alt="Reply comment icon"><a class="joinus-link" id="btnCommentReply" href="#" target="_self">::blogdetailsReply</a></li></ul></div></div></div></div></div>', 0, 1, N' [e-mail] ', NULL, 23) GO SET IDENTITY_INSERT [dbo].[Blog_CommentDetails] OFF GO 

All suggestions are welcome!

I added three insertion requests also below:

Valex, please paste this query into a table that is "INSERT [dbo]. [Blog_CommentDetails] ([CommentID], [CommentUserName], [CommentText], [CommentApprovedByUserID], [CommentPostDocumentID], [CommentDate], [HtmlComment], [CommentIsSpam], [CommentIsApproved], [CommentEmail], [CommentInfo], [ReplyCommentID], [IsRejected]) VALUES (58, N'Vicky, N'Test ', 0, 1, CAST (N'2016-12-02 11: 51: 07.270 "AS DateTime", N'VickyDec2,2016 11:47 AM

Testing

• Answer

', 0, 1, N' vicky@gmail .com ', NULL, NULL, 0) GO INSERT [dbo]. [Blog_CommentDetails] ([CommentID], [CommentUserName], [CommentText], [CommentApprovedByUserID], [CommentPostDocumentID], [CommentDate], [HtmlComment], [CommentIsSpam], [CommentIsApproved], [CommentEmail], [CommentInment], [CommentInment] ]) VALUES (61, N'Billu ', N' This is a test of comments ', 0, 1, CAST (N'2016-12-02 12: 35: 40.220' AS DateTime), N'BilluDec2, 2016 12:34 PM This is a test of comments.

• To answer

', 0, 1, N' [e-mail] ', NULL, 58) GO INSERT [dbo]. [Blog_CommentDetails] ([CommentID], [CommentUserName], [CommentText], [CommentApprovedByUserID], [CommentPostDocumentID], [CommentDate], [HtmlComment], [CommentIsSpam], [CommentIsApproved], [CommentEmail], [CommentInment], [CommentInment] ]) VALUES (62, N'Rakesh ', N' This is rakesh 'test, 0, 1, CAST (N'2016-12-02 12: 37: 42.133' AS DateTime), N'RakeshDec2, 2016 12:36 This rakesh test

• Answer

', 0, 1, N' [e-mail] ', NULL, 58)

Now I am editing my question. Please check the screenshot below:

I used the query below:

 WITH CTE AS ( SELECT CommentID , CommentPostDocumentID , CommentIsApproved, CommentDate , ReplyCommentID , CommentUserName, CommentID AS ThreadID , CAST( CommentID AS VARCHAR( MAX ) ) AS PathStr FROM Blog_CommentDetails AS T WITH(NOLOCK) WHERE ReplyCommentID IS NULL UNION ALL SELECT T.CommentID , t.CommentPostDocumentID , t.CommentIsApproved, T.CommentDate , T.ReplyCommentID , T.CommentUserName, CTE.ThreadID , PathStr + '-'+ CAST( T.ReplyCommentID AS VARCHAR( MAX ) ) AS PathStr FROM Blog_CommentDetails AS T WITH(NOLOCK) JOIN CTE ON T.ReplyCommentID = CTE.CommentID WHERE T.ReplyCommentID IS NOT NULL ) SELECT * FROM CTE WHERE CommentPostDocumentID = 18 AND CommentIsApproved=1 ORDER BY ThreadID , PathStr , CommentDate DESC 

Below is an image to display comments:

The expected structure is as follows:

• Comment by Vikash
• Sid replied to vikash's comment.
• Mega commented on wikash, so the megha comment is higher than Sid under Vikash's parent.
• QE responded to Mega's comment, so it should be higher than Sid, but in the last line.

Answer 1

You must use a recursive CTE. Here is an adaptation from your later question

 WITH CTE AS ( SELECT CommentID, CommentUserName, CommentText,CommentDate,ReplyCommentID, CommentID as ThreadID, CAST(CommentID as varchar(MAX)) as PathStr FROM Blog_CommentDetails as T WHERE ReplyCommentID IS NULL UNION ALL SELECT T.CommentID, T.CommentUserName, t.CommentText,T.CommentDate, T.ReplyCommentID, CTE.ThreadID, PathStr+'-' +CAST(T.ReplyCommentID as varchar(MAX)) as PathStr FROM Blog_CommentDetails as T JOIN CTE ON T.ReplyCommentID = CTE.CommentID WHERE T.ReplyCommentID IS NOT NULL ) SELECT * FROM CTE ORDER BY ThreadID,PathStr,CommentID, CommentDate desc 

Result:

 ╔═══════════╦═════════════════╦═══════════════════════╦═════════════════════════╦════════════════╦══════════╦═════════╗ ║ CommentID ║ CommentUserName ║ CommentText ║ CommentDate ║ ReplyCommentID ║ ThreadID ║ PathStr ║ ╠═══════════╬═════════════════╬═══════════════════════╬═════════════════════════╬════════════════╬══════════╬═════════╣ ║ 22 ║ Vikash ║ This is main comment ║ 2016-11-12 17:36:25.637 ║ NULL ║ 22 ║ 22 ║ ║ 23 ║ Megha k ║ This is reply comment ║ 2016-11-12 17:39:04.250 ║ NULL ║ 23 ║ 23 ║ ║ 24 ║ Siddappa H ║ This is reply text. ║ 2016-11-12 17:39:58.847 ║ 23 ║ 23 ║ 23-23 ║ ║ 26 ║ Vikash ║ This is reply text ║ 2016-11-12 17:41:44.673 ║ 23 ║ 23 ║ 23-23 ║ ║ 25 ║ Suresh P ║ This is reply comment ║ 2016-11-12 17:40:44.470 ║ NULL ║ 25 ║ 25 ║ ╚═══════════╩═════════════════╩═══════════════════════╩═════════════════════════╩════════════════╩══════════╩═════════╝ 

Answer 2

If you want either a complete hierarchy, or even part of it, with the appropriate sequence

EDIT - deleted by Concat () for 2008

 Declare @Top int = 23 --null --<< Sets top of Hier Try 23 Declare @Nest varchar(25) ='|-----' --<< Optional: Added for readability ;with cteP as ( Select Seq = cast(1000+Row_Number() over (Order by CommentID) as varchar(500)) ,CommentID ,ReplyCommentID ,Lvl=1 From [dbo].[Blog_CommentDetails] Where IsNull(@Top,-1) = case when @Top is null then isnull(ReplyCommentID,-1) else CommentID end Union All Select Seq = cast(p.Seq+'.'+cast(1000+Row_Number() over (Order by r.CommentID) as varchar(25)) as varchar(500)) ,r.CommentID ,r.ReplyCommentID ,p.Lvl+1 From [dbo].[Blog_CommentDetails] r Join cteP p on r.ReplyCommentID = p.CommentID) ,cteR1 as (Select *,R1=Row_Number() over (Order By Seq) From cteP) ,cteR2 as (Select A.Seq,A.CommentID,R2=Max(B.R1) From cteR1 A Join cteR1 B on (B.Seq like A.Seq+'%') Group By A.Seq,A.CommentID ) Select A.R1 ,B.R2 ,A.CommentID ,A.ReplyCommentID ,A.Lvl ,CommentText = Replicate(@Nest,A.Lvl-1) + C.CommentText -- Include any other fields from [dbo].[Blog_CommentDetails] alias C From cteR1 A Join cteR2 B on A.CommentID=B.CommentID Join [dbo].[Blog_CommentDetails] C on A.CommentID=C.CommentID Order By A.R1 

Returns

If @Top is set to 23 (for example), the return will be

I must add, cteR2 is not required, but it indicates the region and / or level of the parent or sheet.

Answer 3

You can order it as shown below:

 select coalesce(replycommentid, commentid) as sequence, ... order by sequence, ... 

The parent line will use its own identifier as a sequence.

Answer 4

If I understand what you are looking for, you want each comment to be accompanied by its answers before any (non-response) comment. I think you want just:

 select * from Blog_CommentDetails order by coalesce(ReplyCommentID, commentID), CommentID