Consolidating a row of data, based on previous rows

I'm trying to build a history table out of an audit log (ultimately to build out a type 2 dimension table). Unfortunately, the audit log only records the specific fields being changes. Here's a rough example of what I'm talking about; CREATE TABLE Staff( [ID] int, [Surname] varchar(5), [FirstName] varchar(4), [Office] varchar(9), [Date] varchar(10) ); INSERT INTO Staff ([ID], [Surname], [FirstName], [Office], [Date]) VALUES (001, 'Smith', 'Bill', 'Melbourne', '2015-01-01'), (001, NULL, NULL, 'Sydney', '2015-03-01'), (002, 'Brown', 'Mary', 'Melbourne', '2014-04-01'), (002, 'Jones', NULL, 'Adelaide', '2014-05-01'), (002, NULL, NULL, 'Sydney', '2015-01-01'), (002, NULL, NULL, 'Perth', '2015-03-01'); The first entry for a particular staff member is for when their record is created, and each subsequent record is an update... but only shows the update to the field that was updated*. I want to "fill out" the update row with the rest of the employee record as it currently stands. ie, a result like this; 001, Smith, Bill, Melbourne, 2015-01-01 001, Smith, Bill, Sydney, 2015-03-01 002, Brown, Mary, Melbourne, 2014-04-01 002, Jones, Mary, Adelaide, 2014-05-01 002, Jones, Mary, Sydney, 2015-01-01 002, Jones, Mary, Perth, 2015-03-01 I know I can do this using a while loop or a cursor but I suspect there is probably a more performant option. --- *A NULL always means "value didn't change" rather than "value changed to NULL".