• 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!