Display first parent on the first child and the rest of the children with the same parent display as NULL

Question

I have two tables, Parents and Children in SQL Server.

Parent Table

ParentID    ParentName
----------- ------------------
1           Parent 1
2           Parent 2
3           Parent 3
4           Parent 4
5           Parent 5

Children Table

ChildID     ChildName                           ParentID
----------- ----------------------------------- -----------
1           Child 1                             1
2           Child 2                             1
3           Child 3                             1
4           Child 4                             1
5           Child 5                             2
6           Child 6                             2
7           Child 7                             3
8           Child 8                             3
9           Child 9                             3

How can I query to display first parent on the first child and the rest of the children with the same parent display as NULL?

ChildID     ChildName                   ParentName
----------- --------------------------- -----------------
1           Child 1                     Parent 1
2           Child 2                     NULL
3           Child 3                     NULL
4           Child 4                     NULL
5           Child 5                     Parent 2
6           Child 6                     NULL
7           Child 7                     Parent 3
8           Child 8                     NULL
9           Child 9                     NULL

Answer 1

;with cte as 
  (
  select  childId, 
            ChildName, ParentName,
            p.ParentId, Row_number() over(partition by p.ParentId Order by p.ParentId asc, ChildID asc) row_num  
  from Table20 a join parent p on a.ParentID=p.ParentID
  )
  select  childId, 
            ChildName, 
              CASE row_num WHEN 1 THEN ParentName ELSE NULL END as parentname from cte

output

childId ChildName   parentname
1   Child 1 Parent 1
2   Child 2 NULL
3   Child 3 NULL
4   Child 4 NULL
5   Child 5 Parent 2
6   Child 6 NULL
7   Child 7 Parent 3
8   Child 8 NULL
9   Child 9 NULL

Answer 2

Try below query.

SELECT c.ChildId, c.ChildName, p.ParentName    
FROM    (SELECT childId, 
            ChildName, 
            ParentId, 
            Row_number() over(partition by ParentId Order by ParentId asc, ChildID asc) row_num
    FROM Children) c    
LEFT JOIN Parent p 
 ON c.ParentId = p.ParentId
  AND c.row_num = 1;

Answer 3

Try this one

SELECT c.ChildId, c.ChildName, CASE row_num WHEN 1 THEN p.ParentName ELSE NULL END    
(SELECT childId, ChildName, ParentId, 
        Row_number() over(partition by ParentId Order by ParentId asc, ChildID asc) row_num
FROM Children) c    
JOIN Parent p ON c.ParentId = p.ParentId

Answer 4

Try this:

SELECT C.*
      ,IIF(ROW_NUMBER() OVER (PARTITION BY C.[ParenetID] ORDER BY C.[ChildID]) <> 1, NULL, P.[ParentName])
FROM @Children C
INNER JOIN @Parent P
    ON C.[ParenetID] = P.[ParenetID]

Full working example:

DECLARE @Parent  TABLE
(
    [ParenetID] TINYINT
   ,[ParentName] VARCHAR(12)
);


DECLARE @Children  TABLE
(
    [ChildID] TINYINT
   ,[ChildName] VARCHAR(12)
   ,[ParenetID] TINYINT
);


INSERT INTO @Parent ([ParenetID], [ParentName])
VALUES   (1, 'Parent 1')
        ,(2, 'Parent 2')
        ,(3, 'Parent 3')
        ,(4, 'Parent 4')
        ,(5, 'Parent 5');

INSERT INTO @Children ([ChildID], [ChildName], [ParenetID])
VALUES   (1, 'Child 1', 1)
        ,(2, 'Child 2', 1)
        ,(3, 'Child 3', 1)
        ,(4, 'Child 4', 1)
        ,(5, 'Child 5', 2)
        ,(6, 'Child 6', 2)
        ,(7, 'Child 7', 3)
        ,(8, 'Child 8', 3)
        ,(9, 'Child 9', 3);

SELECT C.*
      ,IIF(ROW_NUMBER() OVER (PARTITION BY C.[ParenetID] ORDER BY C.[ChildID]) <> 1, NULL, P.[ParentName])
FROM @Children C
INNER JOIN @Parent P
    ON C.[ParenetID] = P.[ParenetID]