First time posting here...Please forgive formatting issues. After days of trying to find a similar situation upon which to derive a solution to my problem, I still find myself inside a Rubik's cube. I'm trying to create a "report" that will show student performance on exams and their demographics. Here's the scenario:I have an exam source table with the following columns:Table Name: FOT2012PRE
\[quote\] ItemID int (Primary Key)
ItemNum nchar(10) (Corresponding column name in results table)
ItemStem nvarchar(1000) (This is the question stem.)
IC1 nvarchar(1000) (This is the first answer choice.)
IC2 nvarchar(1000) (This is the second answer choice.)
IC3 nvarchar(1000) (This is the third answer choice.)
IC4 nvarchar(1000) (This is the fourth answer choice.)
PriAns nvarchar(10) (This is the Correct answer choice- N/A for demographic items.)
\[/quote\]There are additional columns to identify graphics, videos, course, unit, demog flag.Here's some of the data in the exam source table:\[quote\] ItemID ItemNum ItemStem IC1 IC2 IC3 IC4 PriAns
1 Item1 What is your current grade level? 9th Grade 10th Grade 11th Grade 12th Grade NULL
2 Item2 What is your gender? Male Female NULL NULL NULL
\[/quote\]As students answer each question, their selection is recorded in a results table specific for that exam. This results table has the following columns:Table Name: FOT2012PRERESULTS
\[quote\] ResultsID int (Primary Key)
StudentID nvarchar(255) (Unique ID for each student taking exam)
LastItem nvarchar(255) (Updated with each answer processed - for exam re-entry)
Item1 nvarchar(255) (Answer recorded for Item1: "C" if correct; Choice if incorrect)
Item2
nvarchar(255) (Answer recorded for Item2: same as for all other questions in the exam)
\[/quote\]Here some sample data from the results table:\[quote\] ResultsID StudentID LastItem Item1 Item2 Item3
743 MD911059935 67 IC2 IC2 C
744 IC2 IC2 C
746 MD911059949 67 IC2 IC2 IC4
934 MD3590986869 46 IC2 IC1 IC3
\[/quote\]Ultimately I want to be able to have my report to look something like this:Item: What is your current grade level?
\[quote\] 9th Grade (number of IC1 responses)
10th Grade (number of IC2 responses)
11th Grade (number of IC3 responses)
12th Grade (number of IC4 responses)
No Response (number of NULL responses)\[/quote\]These results would take the form of a table and also compare results on a local, district, state, and national basis, but I can do the corresponding nested query to handle that. For this solution, I am open to and believe that it's probably best implemented as a stored procedure since there are about 40 exams spread across a lot of states and thousands of students. I can pass table names and other values as needed. Access to the report is individualized based on which exam was administered for a particular class.I appreciate any assistance and suggestions. I must respectfully ask for your indulgence in providing specifics as to any joins or pivots. I am a dinosaur and program in ASP JavaScript. My attempts down that path thus far have only put me inside this cube...and I need some light to find my way out. If I've omitted anything, please let me know.Thank you...
\[quote\] ItemID int (Primary Key)
ItemNum nchar(10) (Corresponding column name in results table)
ItemStem nvarchar(1000) (This is the question stem.)
IC1 nvarchar(1000) (This is the first answer choice.)
IC2 nvarchar(1000) (This is the second answer choice.)
IC3 nvarchar(1000) (This is the third answer choice.)
IC4 nvarchar(1000) (This is the fourth answer choice.)
PriAns nvarchar(10) (This is the Correct answer choice- N/A for demographic items.)
\[/quote\]There are additional columns to identify graphics, videos, course, unit, demog flag.Here's some of the data in the exam source table:\[quote\] ItemID ItemNum ItemStem IC1 IC2 IC3 IC4 PriAns
1 Item1 What is your current grade level? 9th Grade 10th Grade 11th Grade 12th Grade NULL
2 Item2 What is your gender? Male Female NULL NULL NULL
\[/quote\]As students answer each question, their selection is recorded in a results table specific for that exam. This results table has the following columns:Table Name: FOT2012PRERESULTS
\[quote\] ResultsID int (Primary Key)
StudentID nvarchar(255) (Unique ID for each student taking exam)
LastItem nvarchar(255) (Updated with each answer processed - for exam re-entry)
Item1 nvarchar(255) (Answer recorded for Item1: "C" if correct; Choice if incorrect)
Item2
nvarchar(255) (Answer recorded for Item2: same as for all other questions in the exam)
\[/quote\]Here some sample data from the results table:\[quote\] ResultsID StudentID LastItem Item1 Item2 Item3
743 MD911059935 67 IC2 IC2 C
744 IC2 IC2 C
746 MD911059949 67 IC2 IC2 IC4
934 MD3590986869 46 IC2 IC1 IC3
\[/quote\]Ultimately I want to be able to have my report to look something like this:Item: What is your current grade level?
\[quote\] 9th Grade (number of IC1 responses)
10th Grade (number of IC2 responses)
11th Grade (number of IC3 responses)
12th Grade (number of IC4 responses)
No Response (number of NULL responses)\[/quote\]These results would take the form of a table and also compare results on a local, district, state, and national basis, but I can do the corresponding nested query to handle that. For this solution, I am open to and believe that it's probably best implemented as a stored procedure since there are about 40 exams spread across a lot of states and thousands of students. I can pass table names and other values as needed. Access to the report is individualized based on which exam was administered for a particular class.I appreciate any assistance and suggestions. I must respectfully ask for your indulgence in providing specifics as to any joins or pivots. I am a dinosaur and program in ASP JavaScript. My attempts down that path thus far have only put me inside this cube...and I need some light to find my way out. If I've omitted anything, please let me know.Thank you...