SQL Server - Select parent row or child row if no parent

Question

I have a single table, Issue. This table has Id and ChildId columns.

=============
Id | ChildId
1  | NULL
2  | 3
3  | NULL
4  | 1
5  | NULL
6  | NULL
=============

I need to select from this table so that the parent row is returned if it exists else return the child row. So selecting from every row would get:

=============
Id | ChildId
4  | 1
2  | 3
2  | 3
4  | 1
5  | NULL
6  | NULL
=============

I know that this table layout is not good (using ChildId instead of ParentId) but I cannot change the scheme unfortunately.

If you know how to return this data that would be great. Thanks in advance.

Answer 1

Join the same table twice and use a CASE statement:

select
    case when t2.id is not null then t2.id else t1.id end as id,
    case when t2.id is not null then t2.childid else t1.childid end as childid
from 
    tbl t1
left join tbl t2
    on t2.childid = t1.id

id | childid
-: | ------:
 4 |       1
 2 |       3
 2 |       3
 4 |       1
 5 |    null
 6 |    null

db<>fiddle here