Parent Child records select

Mohamed Farook 141 Reputation points
2022-09-01T16:47:05.38+00:00

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

237024-img.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,885 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2022-09-01T17:01:22.687+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

  2. CosmogHong-MSFT 23,641 Reputation points Microsoft Vendor
    2022-09-02T01:17:46.36+00:00

    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:
    237036-image.png

    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.

    1 person found this answer helpful.
    0 comments No comments