Finding last "Non-Null" value in a Pivot query

Question

I have the following query:

 SELECT * FROM 
                (
                    SELECT unix_timestamp, input_raw, tag_id 
                    from [200030].[dbo].inputs
                    WHERE inputs.date_time > dateadd(day,-1,getdate())
                    AND
                    (tag_id = 92164 or tag_id = 92149)
                ) src
                pivot
                (
                    max(input_raw)
                    FOR tag_id IN ([92164], [92149])
                ) piv
                ORDER by unix_timestamp DESC

which is great and works. It gives me the results:

However, I would like the query to do one more thing for me.

Whenever there is a 'NULL' result, I would like the query to replace the 'NULL' with the last 'non-NULL' value in the column.

For example, the first NULL seen in column '92164' would be replaced with '211'.

Also, it is possible that there will be several 'NULL's i a row, so the query would need to keep going up the column until it finds a none NULL.

I have been able to accomplish this with php. Putting the results into a 2D associative array and running a function that finds nulls then loops through to find the last non NULL, but I really want to do this all in SQL if possible. I'd much rather use the

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
  //code     
}

method than assigning several arrays.

Any help?

Thank you

//EDIT

Forgot to add that this only applies if there is any non-nulls above the null value. For example it is acceptable if the first row is NULL.

Answer 1

You can put your pivot query in a CTE and reuse the CTE when you figure out what value to show. You data have null values only in the first column but here is a version that deals with the nulls in both columns.

WITH C AS
(
  SELECT * 
  FROM (
       SELECT unix_timestamp, input_raw, tag_id 
       FROM inputs
       WHERE date_time > dateadd(day,-1,getdate()) AND
             (tag_id = 92164 OR tag_id = 92149)
       ) src
  PIVOT
       (
       MAX(input_raw)
       FOR tag_id IN ([92164], [92149])
       ) piv
)
SELECT C1.unix_timestamp,
       (
       SELECT TOP(1) C2.[92164] 
       FROM C AS C2
       WHERE C1.unix_timestamp <= C2.unix_timestamp AND
             C2.[92164] IS NOT NULL
       ORDER BY C2.unix_timestamp
       ) AS [92164],
       (
       SELECT TOP(1) C2.[92149] 
       FROM C AS C2
       WHERE C1.unix_timestamp <= C2.unix_timestamp AND
             C2.[92149] IS NOT NULL
       ORDER BY C2.unix_timestamp
       ) AS [92149] 
FROM C AS C1
ORDER by C1.unix_timestamp DESC;

If you have a performance problem it might be faster for you to store the result from the CTE in a temp table with a useful clustered key index.

CREATE TABLE #C
(
  unix_timestamp int PRIMARY KEY,
  [92164] int,
  [92149] int
);

INSERT INTO #C
SELECT *
FROM (
      SELECT unix_timestamp, input_raw, tag_id 
      FROM inputs
      WHERE date_time > dateadd(day,-1,getdate()) AND
            (tag_id = 92164 OR tag_id = 92149)
      ) src
PIVOT
      (
      MAX(input_raw)
      FOR tag_id IN ([92164], [92149])
      ) piv;

SELECT C1.unix_timestamp,
       (
       SELECT TOP(1) C2.[92164] 
       FROM #C AS C2
       WHERE C1.unix_timestamp <= C2.unix_timestamp AND
             C2.[92164] IS NOT NULL
       ORDER BY C2.unix_timestamp
       ) AS [92164],
       (
       SELECT TOP(1) C2.[92149] 
       FROM #C AS C2
       WHERE C1.unix_timestamp <= C2.unix_timestamp AND
             C2.[92149] IS NOT NULL
       ORDER BY C2.unix_timestamp
       ) AS [92149]
FROM #C AS C1
ORDER by C1.unix_timestamp DESC;

DROP TABLE #C;