Parent Child records select

Question

Hi,
I want exact result like below image

create table #temp1 (ID INT,Name varchar(50),Comment varchar(500))
create table #temp2 (ID INT,Name varchar(50),Comment varchar(500))

insert into #temp1 (ID,Name,Comment) values (1,'JHON','Parent'),(2,'KING','Parent'),(3,'PRINCE','Parent')
insert into #temp2 (ID,Name,Comment) values (1,'AAA','Child'),(1,'BBB','Child'),(1,'CCC','Child')
,(2,'DDD','Child'),(2,'EEE','Child'),(3,'FFF','Child'),(3,'GGG','Child')

select * from #temp1,#temp2

drop table #temp1,#temp2

I Want exact result like below image

Answer 1

This question was already answered in your previous post. Is there a problem with the solution given?

SELECT
ROW_NUMBER() OVER (ORDER BY ID, ParentId) as SINo,
ParentId,[Name],[Comment]

FROM (
select 0 as ParentId, [Name], [Comment], ID
from #temp1
UNION ALL
select ID as ParentId, [Name], [Comment], ID
from #temp2
) a
ORDER BY 1

Answer 2

Hi @Mohamed Farook
Check this query:

;WITH CTE AS  
(  
 SELECT *,CASE WHEN Comment = 'Parent' THEN 0 ELSE RANK()OVER(ORDER BY ID) END AS ParentID  
 FROM ( SELECT * FROM #temp1  
        UNION ALL   
        SELECT * FROM #temp2 )S  
)  
SELECT ROW_NUMBER()OVER(ORDER BY ID,ParentID,Name)AS SINo,ParentID,Name,Comment  
FROM CTE  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.