Separate columns values as another column

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 <= @totalrecordscount)

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 <= @onestablecount)

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

1 Reply

Know the answer? Post it — somebody with the same question will find it here.