How split row into multiple rows using sqlserver?

Question

My table like this

Name	Address
sri ni vasa Rao	hyderabad plotno:109 Dsnr
Sravan kumar	Guntur HNo:1001
sandhya	Guntur

My Requirement is

Name	Address
sri	Hyderabad
ni	plotNo:109
vasa	Dsnr
Rao
sravan	Guntur
kumar	HNo:1001
sandhya	Guntur

How to solve this one?
Help me

mangesh shelar · Answer

-- Create table for Testing create table #temp12 (Id int UserName varchar(1000)  Address varchar(1000)) -- Insert test Data insert into #temp12 (idUserName Address ) values (1'sri ni vasa Rao''hyderabad plotno:109 Dsnr') insert into #temp12 (idUserName Address ) values (2'Sravan kumar''Guntur HNo:1001') insert into #temp12 (idUserName 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(' '@tmpStr1) set @Cnt = 1 while @DelPos > 0 begin select @tmpStr2 = SUBSTRING (@tmpStr  1@DelPos - 1 )  @tmpStr = SUBSTRING (@tmpStr  @DelPos + 1  8000) set @DelPos = CHARINDEX(' '@tmpStr1) 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(' '@tmpStr1) set @Cnt = 1 while @DelPos > 0 begin select @tmpStr2 = SUBSTRING (@tmpStr  1@DelPos - 1 )  @tmpStr = SUBSTRING (@tmpStr  @DelPos + 1  8000) set @DelPos = CHARINDEX(' '@tmpStr1) 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.Idn.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

Benjamin Kemner · Answer

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