Try this:
create table #Test1(firstname nvarchar(max) ,middlename nvarchar(max),lastname nvarchar(max) )
insert into #Test1 values('Raj','','')
insert into #Test1 values('','Kumar','')
insert into #Test1 values('','','Reddy')
option 1:
with cte as(
select firstname From #Test union all select middlename From #Test union all select lastname From #Test
)
select *From cte where firstname!=''
option 2:
select firstname From #Test where firstname!='' union all select middlename From #Test where middlename!='' union all select lastname From #Test where lastname!=''