SQL Server 2008 R2: Get first not null values from column

In sqlserver, when you have multiple data with some column have null value and you need the row which not null.

So this example will help you.

CREATE TABLE Family
(
    ID int,
    Name varchar(20),
    Gender char(1)
);

INSERT INTO Family VALUES
(1,'Ram','M'),
(2,'Suraj','M'),
(3,'Sunitha','F'),
(4,'Deepika','F'),
(5,'Minakshi','F'),
(6,'Somu','M');

CREATE TABLE Child_parent
(
    Child_ID int,
    Parent_ID int
);

INSERT INTO Child_parent VALUES
(1,2),
(1,3),
(4,5),
(4,6);

Expected Result:
Child_ID ChildName FatherName MotherName -----------------------------------------------  
1        Ram       Suraj      Sunitha 
4        Deepika   Somu       Minakshi


Query to get Desire result with self join as below.



SELECT  cp.Child_ID,
        f1.Name as ChildName,
        max(CASE WHEN f2.Gender = 'M' THEN f2.Name END) FatherName,
        max(CASE WHEN f2.Gender = 'F' THEN f2.Name END) MotherName
FROM Child_parent cp
LEFT JOIN Family f1
ON cp.Child_ID = f1.ID
LEFT JOIN Family f2
ON cp.Parent_ID = f2.ID
group by cp.Child_ID,
        f1.Name 
 order by cp.Child_ID

 Referece link - https://stackoverflow.com/questions/47627228/sql-server-2008-r2-get-first-not-null-values-from-column?answertab=votes#tab-top