fill null values

Code:

use ee
declare @temp Table(
supplierno varchar(3)
,  suppliername varchar(10)
);

Insert into @temp(supplierno, suppliername) Values
('001', 'ABC')
, ('001', '')
, ('001', '')
, ('002', '')
, ('002', '')
,('002', 'BBC');

;with cte1 as
(Select supplierno, max(suppliername) As MaxName 
from @temp Group By supplierno),
cte2 as
(Select c.supplierno, (select cte1.MaxName from Cte1 Where cte1.supplierno = c.supplierno) as [Name]
from @temp c)
Select cte2.supplierno, (Select [Name] from CTE1 Where cte1.supplierno =cte2.supplierno ) as [Name] from Cte2 

Select allOpen in new window

This is just a test. I will shortly post update sql to update the table. Column name ... as [Name] is added

Mike

Final Version... with update... please note the table name here used is TableAB. Please replace it with the table name you have.
Code:

use ee
--create Table TableAB(
--supplierno varchar(3)
--,  suppliername varchar(10)
--);

Insert into TableAB(supplierno, suppliername) Values
('001', 'ABC')
, ('001', '')
, ('001', '')
, ('002', '')
, ('002', '')
,('002', 'BBC');

Select * from TableAB;

;with CTE1 as
(Select supplierno, max(suppliername) As MaxName 
from TableAB Group By supplierno)
Update TableAB  Set suppliername = (Select max(MaxName) from CTE1 Where TableAB.supplierno = CTE1.supplierno ) 

Select * from TableAB;

--Delete from TableAB

Select allOpen in new window

Here at line 15 it runs "Select * from TableAB;" to show the content of the table prior to manipulation. And later at the end, at line 22 it runs "Select * from TableAB;" again to show the updated version of it.

As demonstrate above, the code works fine. Nonetheless, make a back of the table just in case.

Fisrt I apologize if this doesn't work.  I'm an Oracle person but stumbled onto this question and based on the docs, I believe it ports to SQL Server:
https://docs.microsoft.com/en-us/sql/t-sql/functions/first-value-transact-sql

See if this works for you:

select supplierno, first_value(suppliername) over(partition by supplierno order by suppliername) newname
from dimsupplier;

Select allOpen in new window

here is my version

update a
   set a.suppliername = (select top 1 suppliername from dimsupplier b where a.supplierno=b.supplierno and b.suppliername is not null)
  from dimsupplier a
 where suppliername is null;

Select allOpen in new window

Hi Eric,
I tested your solution and the result is:

>>Hi Eric,

FYI:  Not Eric.  My reference is in honor of Netminder.

In SQL Server is a '' the same as a null?  Are you sure the insert is actually inserting null values or empty strings?  From everything I've read, SQL Server treats them as different values.

Hi Chris,

re> I don't see that you are asking for an update statement...

In the original question sam2929 writes:
 
I Want results like below:

supplierno  suppliername
001              ABC
001             ABC
001             ABC
002             BBC
002             BBC
002             BBC

Select allOpen in new window

If we consider this is the data in the table then one can assume it is the original table but updated.
Because the author doesn't state clearly, displaying it via a query will be good enough. I did try your suggested solution, it works beautifully. And your solution can turn into an update query very easily.

sam2929 is missing in action. I hope he will respond by commenting on the posts here.

Thanks,

Mike

here is my select version

select a.supplierno,
       isnull(a.suppliername,
             (select top 1 suppliername from dimsupplier b where a.supplierno=b.supplierno and b.suppliername is not null)
       ) suppliername
  from dimsupplier a

Select allOpen in new window

There is no need to query the table twice when the window functions will only access it once.

There is no need to query the table twice when the window functions will only access it once.

then we can just use lag if performance is the issue here...

select supplierno,
       isnull(suppliername, lag(suppliername) OVER (ORDER BY supplierno, supplierno)) newName
  from #dimsupplier

Select allOpen in new window

ISNULL + LAG: if value is null, the use previous value, ordered by supplierno... if it is not null, use it as is (first value in the group)