How split row into multiple rows using sqlserver?

-- Create table for Testing
create table #temp12 (Id int, UserName varchar(1000) , Address varchar(1000))

-- Insert test Data
insert into #temp12 (id,UserName, Address ) values (1,'sri ni vasa Rao','hyderabad plotno:109 Dsnr')
insert into #temp12 (id,UserName, Address ) values (2,'Sravan kumar','Guntur HNo:1001')
insert into #temp12 (id,UserName, Address ) values (3,'sandhya','Guntur')

select * from #temp12

--- Start

--drop table #tempname
--drop table #tempaddr

Declare @Id int
Declare @UserName Varchar(1000)
Declare @Address Varchar(1000)

Declare @tmpStr varchar(1000)
Declare @tmpStr2 varchar(1000)
Declare @DelPos int
Declare @Cnt int


DECLARE CurSplitLoop CURSOR FOR SELECT ID, UserName, Address FROM #temp12

OPEN CurSplitLoop
FETCH NEXT FROM CurSplitLoop INTO @Id, @UserName , @Address
   
--Create table #temploop (Id int, UserName varchar(1000) , Address varchar(1000), ColOrder int)
Create table #tempName (Id int, UserName varchar(1000) , ColOrder int)
Create table #tempAddr (Id int, Address varchar(1000), ColOrder int)
       
WHILE @@FETCH_STATUS = 0
BEGIN
    --Split UserName
    set @tmpStr = @UserName
    set @DelPos = CHARINDEX(' ',@tmpStr,1)    
    set @Cnt = 1
    while @DelPos > 0
    begin
        select @tmpStr2 = SUBSTRING (@tmpStr , 1,@DelPos - 1 )
        , @tmpStr = SUBSTRING (@tmpStr , @DelPos + 1 , 8000)
        set @DelPos = CHARINDEX(' ',@tmpStr,1)    
        insert into #tempName(Id, UserName, ColOrder) values  (@Id , @tmpStr2 , @Cnt )
        set @Cnt = @Cnt + 1
    end
    insert into #tempName(Id, UserName , ColOrder) values  (@Id , @tmpStr , @Cnt )             
    -- split Address
    set @tmpStr = @Address 
    set @DelPos = CHARINDEX(' ',@tmpStr,1)    
    set @Cnt = 1
    while @DelPos > 0
    begin
        select @tmpStr2 = SUBSTRING (@tmpStr , 1,@DelPos - 1 )
        , @tmpStr = SUBSTRING (@tmpStr , @DelPos + 1 , 8000)
        set @DelPos = CHARINDEX(' ',@tmpStr,1)    
        insert into #tempAddr (Id, Address , ColOrder) values  (@Id , @tmpStr2 , @Cnt )
        set @Cnt = @Cnt + 1
    end
    insert into #tempAddr(Id, Address , ColOrder) values  (@Id , @tmpStr , @Cnt )       
   
    FETCH NEXT FROM CurSplitLoop INTO @Id, @UserName , @Address
END
CLOSE CurSplitLoop
DEALLOCATE CurSplitLoop

select * from #tempName
select * from #tempAddr

select n.Id,n.UserName , isnull(a.Address,'') , n.ColOrder  from
#tempName n left outer join #tempAddr a on n.Id = a.Id and n.ColOrder = a.ColOrder

drop table #tempname
drop table #tempaddr

Do you want to convert the data in the database? I would suggest this. It's hard work to let your program parse the data everytime you select it from database. It is against what databases should do.

It's hard to recognize the fields of address in you data so I can't recommend a structure. Maybe you can provide a european or us example.

regards

Benjamin