Flatten records into a single row where values are not null

You might use a XML trick:

DECLARE @tbl TABLE(COL1 VARCHAR(100),COL2 VARCHAR(100),COL3 VARCHAR(100),COL4 INT,DEPTH INT);
INSERT INTO @tbl VALUES
 (NULL,NULL   ,'Manager',NULL,9)
,(NULL,NULL   ,NULL   ,NULL,8)
,('Jack',NULL   ,NULL   ,36  ,7)
,(NULL,NULL   ,'Employed',28  ,6)
,('James',NULL   ,NULL   ,15  ,5)
,(NULL,'Ericson',NULL   ,NULL,4)
,(NULL,NULL   ,NULL   ,23  ,3)
,('Jack',NULL   ,NULL   ,NULL,2)
,('John','Smith'  ,'Unemployed',45  ,1);

DECLARE @dpth INT=5;

WITH DataAsXml(TheXml) AS
(
    SELECT t.*
    FROM @tbl t
    WHERE t.DEPTH<=@dpth
    ORDER BY t.DEPTH DESC  
    FOR XML PATH('row'),TYPE
)
SELECT TheXml.value('(/row/COL1/text())[1]','varchar(100)') AS COL1 
      ,TheXml.value('(/row/COL2/text())[1]','varchar(100)') AS COL2
      ,TheXml.value('(/row/COL3/text())[1]','varchar(100)') AS COL3
      ,TheXml.value('(/row/COL4/text())[1]','int') AS COL4
      ,TheXml.value('(/row/DEPTH/text())[1]','int') AS DEPTH
FROM DataAsXml;

The intermediate XML looks like this:

<row>
  <COL1>James</COL1>
  <COL4>15</COL4>
  <DEPTH>5</DEPTH>
</row>
<row>
  <COL2>Ericson</COL2>
  <DEPTH>4</DEPTH>
</row>
<row>
  <COL4>23</COL4>
  <DEPTH>3</DEPTH>
</row>
<row>
  <COL1>Jack</COL1>
  <DEPTH>2</DEPTH>
</row>
<row>
  <COL1>John</COL1>
  <COL2>Smith</COL2>
  <COL3>Unemployed</COL3>
  <COL4>45</COL4>
  <DEPTH>1</DEPTH>
</row>

As you can see, XML will omit NULL values by default. The code will sort the list in descending order. Using XQuery to fetch the very first value will return the top-most, non-null value.

I really like Shnugo's solution, but by the time I've seen it I came up with another one, admittedly more cumbersome solution - using a couple of common table expressions and conditional aggregation - so I'm posting it here just because I don't want the time I spent on it feel like a total waist:

DECLARE @Depth int = 5;

WITH CTE1 AS
(
SELECT COL1, COL2, COL3, COL4, DEPTH,
       SIGN(SUM(IIF(COL1 IS NULL, 0, 1)) OVER(ORDER BY Depth DESC)) AS NonNull1, 
       SIGN(SUM(IIF(COL2 IS NULL, 0, 1)) OVER(ORDER BY Depth DESC)) AS NonNull2, 
       SIGN(SUM(IIF(COL3 IS NULL, 0, 1)) OVER(ORDER BY Depth DESC)) AS NonNull3, 
       SIGN(SUM(IIF(COL4 IS NULL, 0, 1)) OVER(ORDER BY Depth DESC)) AS NonNull4
FROM @Table 
WHERE DEPTH <= @Depth
), CTE2 AS 
(
SELECT COL1, COL2, COL3, COL4, DEPTH,
        SUM(NonNull1) OVER(ORDER BY Depth DESC) As S1,
        SUM(NonNull2) OVER(ORDER BY Depth DESC) As S2,
        SUM(NonNull3) OVER(ORDER BY Depth DESC) As S3,
        SUM(NonNull4) OVER(ORDER BY Depth DESC) As S4
FROM CTE1
)

SELECT  MAX(IIF(S1 = 1, Col1, NULL)) As Col1,
        MAX(IIF(S2 = 1, Col2, NULL)) As Col2,
        MAX(IIF(S3 = 1, Col3, NULL)) As Col3,
        MAX(IIF(S4 = 1, Col4, NULL)) As Col4,
        MAX(DEPTH) As Depth
FROM CTE2 

The first cte adds columns containing 0 until the first non-null value appears, then 1.
The second cte sums these columns, so S1, S2 etc' will contain 0, 1, 2... etc'.
The final selects only values where S1, S2 etc' equals 1 - which is the first non-null value of each column.

I'll add this as another answer as it follows a completely different idea:

DECLARE @tbl TABLE(COL1 VARCHAR(100),COL2 VARCHAR(100),COL3 VARCHAR(100),COL4 INT,DEPTH INT);
INSERT INTO @tbl VALUES
 (NULL,NULL   ,'Manager',NULL,9)
,(NULL,NULL   ,NULL   ,NULL,8)
,('Jack',NULL   ,NULL   ,36  ,7)
,(NULL,NULL   ,'Employed',28  ,6)
,('James',NULL   ,NULL   ,15  ,5)
,(NULL,'Ericson',NULL   ,NULL,4)
,(NULL,NULL   ,NULL   ,23  ,3)
,('Jack',NULL   ,NULL   ,NULL,2)
,('John','Smith'  ,'Unemployed',45  ,1);

DECLARE @dpth INT=5;

--A recursive CTE will travers down the lines to fetch (and hold) the first non-null value:

WITH cte AS
(
    SELECT t.*
          ,ROW_NUMBER() OVER(ORDER BY t.DEPTH DESC) AS RowIndex
    FROM @tbl t
    WHERE t.DEPTH<=@dpth
)
,recCTE AS
(
    SELECT 1 AS rwInx, cte.* FROM cte WHERE RowIndex=1
    UNION ALL
    SELECT rc.rwInx+1
          ,ISNULL(rc.COL1,c.COL1) AS COL1
          ,ISNULL(rc.COL2,c.COL2) AS COL2
          ,ISNULL(rc.COL3,c.COL3) AS COL3
          ,ISNULL(rc.COL4,c.COL4) AS COL4
          ,ISNULL(rc.DEPTH,c.DEPTH) AS DEPTH
          ,c.RowIndex
    FROM cte c
    INNER JOIN recCTE rc ON c.RowIndex=rc.rwInx+1
)
SELECT TOP 1 * 
FROM recCTE
ORDER BY RowIndex DESC;

Hint: Take away the TOP 1 to observe the progressive "filling" of the list.