Separate columns values as another column

Question

Hi, I found the following requirement some where in the google, please help me to acheive it. Input ID Tim 1 9:05:00 2 9:10:00 1 10:00:00 2 10:15:00 1 11:00:00 1 11:30:00 2 11:45:00 2 11:50:00 Output: 1 9:05:00 2 9:10:00 1 10:00:00 2 10:15:00 1 11:00:00 2 11:45:00 1 11:30:00 2 11:50:00

Sreekanth Reddy · Answer

declare @totalrecordscount int declare @loopval int declare @currecordid int select @totalrecordscount = count(*) from tablename; --Create 1's temp table declare @onestable TABLE(Id int,Time datetime) --Create 2's temp table declare @twostable TABLE(Id int,Time datetime) --Create Final Result Temp table declare @resulttable TABLE(InId int,InTime datetime,OutId int,OutTime datetime) select @loopval = 1 while(@loopval BEGIN selet @currecordid = Id from tablename; if @currecordid = 1 insert into @onestable select ROW_NUMBER() AS rn,* from tablename where rn=@loopval; else insert into @twostable select ROW_NUMBER() AS rn,* from tablename where rn=@loopval; select @loopval = @loopval + 1 END --By here we will get all 1's to @onestable table and all 2's to @twostable table --get rows count of @onestable table declare @onestablecount int select @onestablecount =count(*) from @onestable --Now let's loop through the @onestable and fill the details into our @resulttable table declare @inid int declare @intime datetime declare @outid int declare @outtime datetime select @loopval = 1 while(@loopval BEGIN select @inid = Id from @onestable select @intime = Time from @onestable select @outid = Id from @twostable select @outtime = Time from @twostable insert into @resulttable(InId,InTime,OutId,OutTime) values(@inid,@intime,@outid,@outtime) select @loopval = @loopval + 1 END select * from @resulttable

ID	Tim
1	9:05:00
2	9:10:00
1	10:00:00
2	10:15:00
1	11:00:00
1	11:30:00
2	11:45:00
2	11:50:00