I am having tough time to figure out this please help me I have time in/out sql query I have a table looks like below. there are four columns display time in/out info such as...
Date Day Day TimeStamp CheckType
10/11/2014 Sat 8:30 am in
10/11/2014 Sat 11:30am out
10/11/2014 Sat 1:30pm in
10/11/2014 Sat out
10/12/2014 Sun 9:00am in
10/12/2014 Sun 11:20pm out
10/12/2014 Sun 5:20pm out
10/13/2014 Mon 8:00am in
10/13/2014 Mon 6:10pm in
so whoever checkin or checkout then the record will display the result in order and if someone is supposed to check out but accidently pressed in button then this will display as it is (in) or if someone forget to check out then that space will show blank
I am trying to convert rows into column and display such information in below
Date Day Time Type Time Type Time Type Time Type etc-----
10/11/2014 Sat 8:30am in 11:30am out 1:30pm in
10/12/2014 Sun 9:00am in 11:20am out 1:20pm in 6:20pm in
10/13/2014 Mon 8:00am in 6:10pm out
10/14/2014 Tus 8:20am in
etc
I have tried to use pivot
select Date, Day, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10] etc
from
(
select Date, Day, Stamptime, CheckTime, userID
from a table
)
pivot
(
max(StampTime)
for stamptime in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10] etc
) as PivotTable
can anyone explain how to convert the rows into columns I have spent many days already.