Same Date and Various Time Select in Single Row

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You need to pivot the results:

WITH SampleData

AS (

SELECT EmpCode,

CAST(ShiftDateTime AS DateTime) AS ShiftDateTime,

RN = ROW_NUMBER() OVER (PARTITION BY EmpCode, CAST(ShiftDateTime AS Date) ORDER BY ShiftDateTime)

FROM (VALUES

('2058', '01/02/2011 13:43')

,('2058', '01/02/2011 20:12')

,('2058', '01/03/2011 14:04')

,('2058', '01/03/2011 20:13')

,('2058', '01/04/2011 14:09')

,('2058', '01/04/2011 20:12')

) AS Data (EmpCode,ShiftDateTime)

)

SELECT EmpCode, ShiftDate, [1], [2]

FROM (

SELECT EmpCode,

CAST(ShiftDateTime AS Date) AS ShiftDate,

CONVERT(char(5), ShiftDateTime, 114) AS ShiftTime,

RN

FROM SampleData

) AS src

PIVOT( MIN(ShiftTime) FOR RN IN([1], [2]) ) AS pvt

Hope this helps

Gianluca

-- Gianluca Sartori

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Please keep to one thread

Clear Sky SQL
My Blog[/url]

tamil.selvanmca Ten Centuries Points: 1367 More actions · Answer 3

Ten Centuries

Points: 1367

September 2, 2011 at 6:27 am

Thanks