There are 2 tables Employee and Timing input is
EMPCODE LOGIN LOGOUT TRANSDATE 1001 9.30 17.30 20/01/2014 1002 1.30 22.30 20/01/2014 EXPECTED OUTPUT **EMPCODE INOUT(Login/Logout) TRANSDATE** 1001 9.30 20/01/2014 1001 17.30 20/01/2014 1002 10.30 21/01/2014 1002 22.30 21/01/2014 Please provide sql code?
You can use CROSS APPLY to unpivot the columns:
SELECT T.EmpCode, i.InOut, T.TransDate
FROM Timing T
CROSS APPLY (VALUES (T.Login), (T.Logout)) i (InOut);
You could also use UNPIVOT:
SELECT upvt.EmpCode, upvt.InOut, upvt.TransDate
FROM Timing T
UNPIVOT
( InOut
FOR [Type] IN (Login, Logout)
) upvt;
Try this:-
Select Empcode,[InOUT],[TRANSDATE]
from
(
Select EmpCode, Login as [INOUT] ,[TRANSDATE]
from Table1
union all
Select EmpCode, Logout as [INOUT] ,[TRANSDATE]
from Table1
)a
order by empcode
