Davees John Baclay Davees John Baclay -3 years ago 130
SQL Question

SQL Fill blank rows with the first data per group

This is the result set of my raw data.
what I want is to fill the next blank row for column
name with the first name per group.

in this example rowid 1881 and 1879 should be filled
MAR ROXAS, and 1881-1887 filled into RODRIGO DUTERTE and so on.

enter image description here

Answer Source

The ideal way is lag(ignore nulls), but SQL Server doesn't support that. Instead, you can use two levels of window functions:

select max(name) over (partition by name_rowid) as new_name
from (select t.*,
             max(case when name is not null then rowid end) over (order by rowid) as name_rowid
      from billtrans t
     ) t;

The above works in SQL Server 2012+. In SQL Server 2008, you can use much less efficient methods, such as outer apply:

select t.*, t2.name as new_name
from billtrans t outer apply
     (select top 1 t2
      from billtrans t2
      where t2.rowid <= t.rowid and t2.name is not null
      order by t2.rowid desc
     ) t2;

You can also phrase this using a similarly structured correlated subquery.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download