Sorry for the cross posting but I am getting desperate now.

I have a table with 11 fields as follow:

Column Name Data Type Length

strEmployeeID varchar 9
strReportTime_Mon varchar 12
strReportTime_Tue varchar 12
strReportTime_Wed varchar 12
strReportTime_Thu varchar 12
strReportTime_Fri varchar 12
strCommute_Mon varchar 2
strCommute_Tue varchar 2
strCommute_Wed varchar 2
strCommute_Thu varchar 2
strCommute_Fri varchar 2

All the strReportTime are in actually time like 06:00 AM or 06:30 AM or 06:00 PM, etc etc.

Possible data for StrCommute are A through Z and AA, BB and CC. Here is a sample data

091100867 08:00 AM 08:00 AM 08:00 AM 08:00 AM 07:00 AM A A A A A
091098216 08:00 AM 08:00 AM 08:00 AM 08:00 AM 08:00 AM Q Q Q Q Q
091456272 07:00 AM 07:30 AM 07:00 AM 07:00 AM 08:00 AM A A A A A
091748216 09:00 AM 09:00 AM 09:00 AM 09:00 AM 09:00 AM S S S S S
090054234 08:00 AM 08:00 AM 08:00 AM 08:00 AM 08:00 AM A A A A A
090027502 02:30 PM 02:30 PM 02:30 PM 02:30 PM <Null> B B B B CC
091131059 15:00 PM 08:00 AM 07:00 AM 07:00 AM 07:00 AM A A A A CC

Now goal is to find the sum of count of all the commute methods for each day and total for week as follows:

Mode Mon Tues Wed Thu Fri Total

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
BB
CC

Any help would be appreciated.

Thanks.

Habib.

Hi.

For Mode A and column Mon you can do this:

select count(strCommute_Mon) from <table_name>
where strCommute_Mon = 'A'

You can insert result data into a table and do a while do fill all table cells.

This is one option.

See "Rotating Table in Sql Server " Topic in BOL or MSDN. There is an example like this one.

B. Anant