Sql: How to Fill Empty Cells with Previous Row Value

SQL: How to fill empty cells with previous row value?

Faiz,

how about the following query, it does what you want as far as I understand it. The comments explain each step. Take a look at CTEs on Books Online. This example could even be changed to use the new MERGE command for SQL 2008.

/* Test Data & Table */
DECLARE @Customers TABLE
    (Dates datetime,
     Customer integer,
     Value integer) 

    INSERT  INTO @Customers
    VALUES  ('20100101', 1, 12),
        ('20100101', 2, NULL),
        ('20100101', 3, 32),
        ('20100101', 4, 42),
        ('20100101', 5, 15),
        ('20100102', 1, NULL),
        ('20100102', 2, NULL),
        ('20100102', 3, 39),
        ('20100102', 4, NULL),
        ('20100102', 5, 16),
        ('20100103', 1, 13),
        ('20100103', 2, 24),
        ('20100103', 3, NULL),
        ('20100103', 4, NULL),
        ('20100103', 5, 21),
        ('20100104', 1, 14),
        ('20100104', 2, NULL),
        ('20100104', 3, NULL),
        ('20100104', 4, 65),
        ('20100104', 5, 23) ;

/* CustCTE - This gives us a RowNum to allow us to build the recursive CTE CleanCust */
WITH    CustCTE
          AS (SELECT    Customer,
                        Value,
                        Dates,
                        ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY Dates) RowNum
              FROM      @Customers),

/* CleanCust - A recursive CTE. This runs down the list of values for each customer, checking the Value column, if it is null it gets the previous non NULL value.*/
        CleanCust
          AS (SELECT    Customer,
                        ISNULL(Value, 0) Value, /* Ensure we start with no NULL values for each customer */
                        Dates,
                        RowNum
              FROM      CustCte cur
              WHERE     RowNum = 1
              UNION ALL
              SELECT    Curr.Customer,
                        ISNULL(Curr.Value, prev.Value) Value,
                        Curr.Dates,
                        Curr.RowNum
              FROM      CustCte curr
              INNER JOIN CleanCust prev ON curr.Customer = prev.Customer
                                           AND curr.RowNum = prev.RowNum + 1)

/* Update the base table using the result set from the recursive CTE */
    UPDATE trg
    SET Value = src.Value
    FROM    @Customers trg
    INNER JOIN CleanCust src ON trg.Customer = src.Customer
                                AND trg.Dates = src.Dates

/* Display the results */
SELECT * FROM @Customers

SQL: How to fill empty cells with previous row value on basis of condition?

The LAG example as given by gordy is the simplest as long as you have it. Note though that you cannot use it directly to update your table. Windowed functions can only appear in SELECT or ORDER BY clause, so you would need a temporary table.

For older versions you need a cursor. Something like:

declare @demo table
(
id varchar (10),
number int,
oxy_id varchar(2)
)

INSERT INTO @demo VALUES ('308_2123', 36, 'ZY')
INSERT INTO @demo VALUES ('308_2123', 36, NULL)
INSERT INTO @demo VALUES ('308_2123', 37, NULL)
INSERT INTO @demo VALUES ('308_2123', 37, NULL)
INSERT INTO @demo VALUES ('308_2123', 38, 'WY')
INSERT INTO @demo VALUES ('308_2123', 38, 'WY')
INSERT INTO @demo VALUES ('308_2123', 38, NULL)
INSERT INTO @demo VALUES ('308_2123', 39, NULL)
INSERT INTO @demo VALUES ('309_5647', 30, 'AB')
INSERT INTO @demo VALUES ('309_5647', 30, NULL)
INSERT INTO @demo VALUES ('309_5647', 31, NULL)
INSERT INTO @demo VALUES ('309_5647', 32, 'BC')
INSERT INTO @demo VALUES ('310_8897', 20, 'CD')
INSERT INTO @demo VALUES ('310_8897', 21, 'DC')
INSERT INTO @demo VALUES ('310_8897', 22, NULL)
INSERT INTO @demo VALUES ('310_8897', 23, NULL)
INSERT INTO @demo VALUES ('310_8897', 23, NULL)
INSERT INTO @demo VALUES ('311_6789', 1, NULL)
INSERT INTO @demo VALUES ('311_6789', 1, NULL)
INSERT INTO @demo VALUES ('311_6789', 2, 'EF')
INSERT INTO @demo VALUES ('311_6789', 3, 'GH')
INSERT INTO @demo VALUES ('311_6789', 3, NULL)
INSERT INTO @demo VALUES ('312_9874', 1, 'HK')
INSERT INTO @demo VALUES ('312_9874', 1, 'KY')
INSERT INTO @demo VALUES ('312_9874', 1, NULL)
INSERT INTO @demo VALUES ('312_9874', 1, 'YY')

DECLARE @id varchar(10)
DECLARE @oxy_ID varchar(2)
declare @prevOxyID varchar(10) = NULL
declare @number int
DECLARE @previd varchar(10) = NULL

DECLARE cur CURSOR FOR
(SELECT d.id, d.number, d.oxy_id FROM @demo d) 

OPEN cur

FETCH NEXT FROM cur into 
    @id, @number, @oxy_id

WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @oxy_id IS NULL
            BEGIN
                if @prevOxyID IS NOT NULL 
                    BEGIN
                        IF @id = @previd
                            BEGIN
                                UPDATE @demo SET oxy_id = @prevOxyID 
                                WHERE id = @id AND number = @number AND oxy_id IS NULL
                            END
                        ELSE
                            BEGIN
                                SET @prevOxyID = NULL
                            END
                    END
                SET @previd = @id
            END
        ELSE
            BEGIN
                SET @previd = @id
                SET @prevOxyID = @oxy_ID
            END
        FETCH NEXT FROM cur into 
        @id, @number, @oxy_id
    END

close cur
deallocate cur

SELECT * FROM @demo

Please note that you cannot use order by in a cursor. The data must already be in the order as shown on your image. If the data in the table is not in this order then again, you will need to use a temporary table with the records inserted in the right order, and then perform the cursor on the temporary table, and finally update the original table from the temporary one.

EDIT

OK So no cursor version. As mentioned by gordy, the problem with LAG is the repeated numbers. This same problem restricts the use of UPDATE, since there is no unique identifier for a row. Instead I have to insert the results into a temporary table, delete the originals and then re-insert from temp. If you do in fact have a unique key, then please replace this delete and insert with an UPDATE. The following solution, whilst a bit long-winded, does get around the problems, and according to my research should work on Amazon Redshift, but I do not have access to test. I will not repeat the inserts, please copy from above.

declare @demo table
(
id varchar (10),
number int,
oxy_id varchar(2)
)

create table allrownums
(
id varchar (10),
number int,
oxy_id varchar(2),
rownum int
)

INSERT INTO allrownums
SELECT id, number, oxy_id, ROW_NUMBER() OVER (ORDER BY id, number) AS rownum
FROM @demo;

create table allnotnullrows
(
id varchar (10),
number int,
oxy_id varchar(2),
rownum int
)

INSERT INTO allnotnullrows 
SELECT * FROM allrownums 
WHERE oxy_id IS NOT NULL

create table maxrownums
(
id varchar (10),
rownum int,
maxrownum int
)
INSERT INTO maxrownums
SELECT a.id, a.rownum, Max(n.rownum)
FROM allrownums a INNER JOIN allnotnullrows n
ON n.id = a.id WHERE a.rownum >= n.rownum
GROUP BY a.id, a.rownum 

create table tempresults
(
id varchar (10),
number int,
oxy_id varchar(2)
)  
INSERT INTO tempresults
SELECT a.id, a.number, coalesce(a.oxy_id, n.oxy_id) as oxy_id
FROM allrownums a
LEFT JOIN maxrownums m
ON m.rownum = a.rownum
LEFT JOIN  allnotnullrows n
ON a.id = n.id 
and n.rownum = m.maxrownum

DELETE FROM @demo;

INSERT INTO @demo SELECT * FROM tempresults;

DROP TABLE tempresults;
DROP TABLE allrownums;
DROP TABLE allnotnullrows;
DROP TABLE maxrownums;

SELECT * FROM @demo;

Fill blank in table with data from previous row SQL

A mysql query that uses a variable to keep track of the most recent non-null value

SELECT t1.id, 
(CASE WHEN Value IS NULL
THEN @prevValue
ELSE @prevValue := Value END) Value
FROM myValues t1
CROSS JOIN (SELECT @prevValue := NULL) t2
ORDER BY t1.id

How do I fill null columns with values from the previous row?

In BigQuery use LAST_VALUE() with the IGNORE NULLS option and COALESCE():

select timestamp,
       COALESCE(owner_id, last_value(owner_id ignore nulls) over (order by timestamp)) as owner_id,
       COALESCE(owner_assigneddate, LAST_VALUE(owner_assigneddate IGNORE NULLS) OVER (ORDER BY TIMESTAMP)) as owner_assigneddate,
       COALESCE(lastmodifieddate, LAST_VALUE(lastmodifieddate IGNORE NULLS) OVER (ORDER BY TIMESTAMP)) as lastmodifieddate
from cte order by timestamp asc 

Pandas(Python) : Fill empty cells with with previous row value?

First, replace your empty cells with NaNs:

df[df[0]==""] = np.NaN

Now, Use ffill():

df.fillna(method='ffill')
#       0
#0  Text
#1    30
#2    30
#3    30
#4    31
#5  Text
#6    31
#7    31
#8    31
#9    32

What SQLite command can replicate empty fields from a prior row?

After you have imported the table, you can update the empty column values by using a correlated subquery:

UPDATE tablename AS t1
SET x = (
  SELECT t2.x 
  FROM tablename t2 
  WHERE t2.n < t1.n AND COALESCE(t2.x, '') <> '' 
  ORDER BY t2.n DESC LIMIT 1
)
WHERE COALESCE(t1.x, '') = '';

See the demo.

---

Related Topics