Counting Across a Row

Question:
I have a table that stores test answers. It looks something like this:

Student Date_taken Q1 Q2 Q3 ... Q78

If each question (Q%) is true/false (ENUM(”,’T’,’F’)), how do I structure a select statement to tally the total number of “trues” for each student on a particular test?

Answer:
SQL is a set-oriented language, so calculations on columns in a single row are difficult. I took a simplified version of your question and created a table like this:

StudentID int,Q1 char(1),Q2 char(1),Q3 char(3) 

With a data set like this:

StudentID   Q1  Q2  Q31           T    T   F2           F    F   F3           T    T   T

The query below takes the columnar data and creates a row for each quarter in a temporary table:

CREATE PROCEDURE CalcGrades ASSET NOCOUNT ONDECLARE @StudentID int, @Q1 char(1), @Q2 char(1), @Q3 char(1)

Create a cursor from the original table:

DECLARE c  CURSOR FOR SELECT *FROM GradesORDER BY StudentIDCREATE TABLE #Grades (IndexCol int identity (0,1), StudentID int,
 Quarter char(2), TrueFalse char(1))OPEN cFETCH NEXT FROM cINTO @StudentID, @Q1, @Q2, @Q3  WHILE @@FETCH_STATUS = 0  BEGIN    INSERT #Grades (StudentID, Quarter, TrueFalse) 
VALUES (@StudentID, 'Q1', @Q1)    INSERT #Grades (StudentID, Quarter, TrueFalse) 
VALUES (@StudentID, 'Q2', @Q2)    INSERT #Grades (StudentID, Quarter, TrueFalse) 
VALUES (@StudentID, 'Q3', @Q3)  FETCH NEXT FROM c      INTO @StudentID, @Q1, @Q2, @Q3        ENDSELECT StudentID, Trues=Count(CASE WHEN TrueFalse= 
'T' THEN 1 END), Falses=Count(CASE WHEN TrueFalse= 'F' THEN 1 END)FROM #GradesGROUP BY StudentIDORDER BY StudentIDCLOSE cDEALLOCATE c RETURN 0

Then you can do a select with the aggregate function COUNT(), which will produce this result:

StudentID   Trues       Falses      ----------- ----------- ----------- 1           2           12           0           33           3           0

Addendum:
The person who posted this question to me in the first place sent me a query based on this one that creates the same results, but without using a cursor. It looks like this:

SELECT StudentID,  (CASE Q1 WHEN 'T' THEN 1 ELSE 0 END)+ (CASE Q2 WHEN 'T' THEN 1 ELSE 0 END)+ (CASE Q3 WHEN 'T' THEN 1 ELSE 0 END) AS Trues , (CASE Q1 WHEN 'F' THEN 1 ELSE 0 END)+ (CASE Q2 WHEN 'F' THEN 1 ELSE 0 END)+ (CASE Q3 WHEN 'F' THEN 1 ELSE 0 END) AS Falses FROM Grades