Try this !..
create table #tbl_split (id int identity(1,1),N1 varchar(100),N2 varchar(100),N3 varchar(100),N4 varchar(100))
insert into #tbl_split values('a','b','c','d')
insert into #tbl_split values('x','y','z','')
insert into #tbl_split values('m','n','','')
with cte as
(
select unpvt.id,unpvt.value from #tbl_split
unpivot (value for attribute in (N1,N2,N3,N4))unpvt)
select id,value from cte where value!=''