How to add the same value in all rows when the column value is NULL

Question

I have a table with data like below and would like to see the col4 and col5 values repeated for all the rows and do not need col2 and col3 values with Null.

create table #table1(
ID int,
State varchar(50),
Col2 varchar(10),
Col3 varchar(10),
Col4 varchar(10),
Col5 varchar(10)
)


insert into #table1 
select 1,'TX',Null,NUll,'EEE','1234'
UNION ALL
select 1,'TX','AAA' ,'3456',NULL,NULL            
UNION ALL
select 1,'TX','BNH' ,'3467',NULL,NULL  
UNION ALL
select 1,'TX','GFR' ,'1356',NULL,NULL  
UNION ALL
select 1,'TX','UPY' ,'2345',NULL,NULL 

If I do Max on columns, I get just one row as a result

select ID,State,
MAX(Col2) as Col2,
MAX(Col3) as Col3,
MAX(Col4) as Col4,
MAX(Col5) as Col5
from #table1
group by ID,State

Result:
ID  State   Col2    Col3    Col4    Col5
1   TX      UPY     3467    EEE     1234

final out put expected is:

ID  State  col2  col3   Col4   Col5
1   TX     AAA   3456   EEE   1234
1   TX     BNH   3467   EEE   1234
1   TX     GFR   1356   EEE   1234
1   TX     UPY   2345   EEE   1234

Can anyone please help me ?

Answer 1

IsNull Function will fulfill your requirements.

select id, state,col2,col3
  ,isnull(col4, (select max(col4) from #table1)) col4
  ,isnull(col5, (select max(col5) from #table1)) col5
from #table1
where col2 is not null and col3 is not null