Select sort by Parent,Child

Question

Hi,

I need parent , child row

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

Answer 1

Try this query:

;  
with Q as  
(  
    select *, 0 as c   
    from #temp1  
    union all   
    select *, 1 as c   
    from #temp2  
)  
select row_number() over (order by ID, c) as SlNo, ID, case c when 0 then 0 else ID end as ParentID, Name, Comment  
from Q  
order by ID, c  

It assumes that #temp1 contains parents and #temp2 contains children.

Answer 2

Hi @Mohamed Farook
Please check this query:

;WITH CTE1 AS  
(  
 SELECT T1.ID,T1.Name AS PName,T1.Comment AS PComment,T2.Name AS CName,T2.Comment AS CComment   
 FROM #temp1 T1 JOIN #temp2 T2 ON T1.ID=T2.ID  
),CTE2 AS  
(  
 SELECT DISTINCT ID, CASE WHEN C.Comment='Parent' THEN 0 ELSE ID END AS ParentID,C.Name,C.Comment  
 FROM CTE1 CROSS APPLY(VALUES(PName,PComment),(CName,CComment))C(Name,Comment)  
)  
SELECT ROW_NUMBER()OVER(ORDER BY ID,ParentID,Name)AS SINo,*  
FROM CTE2  

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.