Click here to Skip to main content
15,896,450 members
Please Sign up or sign in to vote.
2.50/5 (2 votes)
See more:
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
SQL
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.
Posted
Updated 12-Oct-14 10:05am
v2
Comments
CHill60 12-Oct-14 7:41am    
So what's wrong with the query you already have?
CHill60 12-Oct-14 8:05am    
One problem you do have is the use of reserved words for column names. Either surround them with square brackets e.g. select [Date], [Day] etc OR choose more appropriate names (recommended).
Also - search CodeProject for similar questions and look at the responses - this is a popular homework assignment
Maciej Los 12-Oct-14 16:12pm    
Please, provide more details (complete table structure and piece of data).
[no name] 15-Oct-14 16:17pm    
If this is not your favorite example of Pivot? :-) Bruno
Maciej Los 15-Oct-14 16:20pm    
Yes, it is. Please, see my answer ;)

1 solution

Please, see my past answers:
how to create Pivot table[^]
Dynamic Create table based on another Table ?[^]
Need help in SQL query used in attandance system[^]

Next time, please use "Improve question" widget and provide more details if somebody ask you to do that.
 
Share this answer
 
Comments
[no name] 15-Oct-14 16:22pm    
And 5. Regards, Bruno
Maciej Los 15-Oct-14 16:24pm    
Thank you, Bruno ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900