Luis Cazares (1/20/2016)
Just an advice, this should perform better than the UNION ALL approach and give the same results.
set @query = 'SELECT EmployeeID
,AttType
,' + @cols + '
from
(
selectEmployeeID
,AttType
,AttTime
,AttendanceDate
from AttendanceMovement
CROSS APPLY (VALUES( ''In'', TimeIn),
( ''Out'', TimeOut)) x(AttType, AttTime)
) x
pivot
(
max(AttTime)
for AttendanceDate in (' + @cols + ')
) p
'
Find a detailed explanation in here: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
Thank you for this, Yes, The performance will be a bit of a problem because i will be deploying it on a local machine with SQL express. I will be using the Cross Apply as per your advice. Thank you so much!