Create Table Dx (id int,dept int,flag int)
Insert Into Dx(id,dept,flag)
Values(000,12,0),(000,45,1),(141,78,0),(141,5,1),(000,45,0),(000,9,1)
Select * from Dx
Select x.id,old_dept,New_dept from
(Select id,dept as old_dept from Dx where flag=0)x
Inner join
(select id,dept as New_dept from Dx Where flag=1)y
on x.id=y.id
Where old_dept!=New_dept
OUTPUT:
id old_dept New_dept
0 12 45
141 78 5
0 12 9
0 45 9
Or if your Data is Consistent as you shown then you can use this Query..
Select x.id,old_dept,New_dept from
(Select id,dept as old_dept,ROW_NUMBER()over(order by id) as row from Dx where flag=0)x
Inner join
(select id,dept as New_dept,ROW_NUMBER()over(order by id) as row from Dx Where flag=1)y
on x.row=y.row
Where old_dept!=New_dept
OUTPUT:
id old_dept New_dept
0 12 45
0 45 9
141 78 5
