May 13, 2003 at 4:50 pm
I am familiar with SQL for basic queries but having trouble with the more complex one I am trying to do. I really need help if
anyone can give me guidance here.
Starting from the table/results I need to generate.
The results should have columns that start with Student_id followed by a dynamic list of testnames drawn from a tests table.
Eg., Columns ...
Student_id | [testname1] | [testname2] | etc.
The data rows will list the student's id followed by scores on the given tests.
[76453388] | [65] | [76] | etc.
[65677777] | [33] | [67] | etc.
So the final table/results should look like:
Student_id | [testname1] | [testname2] | etc.
------------------------------------------------
[76453388] | [65] | [76] | etc.
[65677777] | [33] | [67] | etc.
[76456672] | [18] | [43] | etc.
The tables where data must be drawn are as follows:
Student: (Student_id, etc.) - basic list of students
Tests: (Test_id, Admin_Id, Test_Name, etc.) - list of numerous tests any of which a student might take during the year
Scores: (Score_id, Student_id, Test_id, Admin_id, Score, etc.) - list of scores of a student on a particular test.
Admins: (Admin_id, Admin_date) - administrations of sets of tests by date.
The challenge I have is to generate results in the form that I gave the top example above.
Starting with the columns of the results - the testnames listed as columns must be drawn from the tests table by Admin_id.
Eg., "select testname from tests where Admin_id = 3"
If the results were "Fluency", "Math", and "Reading" then the columns of the results would look like:
Student_id | Fluency | Math | Reading |etc.
Then I need results rows to list the scores of every student logged in the scores table with Admin_id = 3.
Eg., "Select s.Student_id, s.score, t.testname from scores s, tests t on s.test_id = t.test_id where s.Admin_id = 3"
The results though of this query would look like:
[76453388] | [23] | Fluency
[76453388] | [34] | Math
[76453388] | [56] | Reading
[65677777] | [76] | Fluency
[65677777] | [67] | Math
[65677777] | [98] | Reading
...
Now I need this data to be grouped such that the rows in the final table are 1 per student and each score is under the testname
in the column.
So the question is how to (if possible) write a query that returns the desired results:
Eg:
Student_id | Fluency | Math | Reading |
------------------------------------------------
[76453388] | [23] | [34] | [56] |
[65677777] | [76] | [67] | [98] |
etc...
Thanks for any help. Hope this makes sense as I've tried to explain it.
May 13, 2003 at 8:51 pm
You'll have to build a dynamic sql statement in a loop. Loop on the number of tests creating a select statement like this:
declare @SqlClause as varchar(500)
select @SqlClause = 'select StudentId, '
--in cursor loop
fetch into @TestNameVariable
@SqlClause = @SqlClause + @TestNameVariable
fetch next...
--then execute the @SqlClause
exec( @SqlClause)
Darren
Darren
May 13, 2003 at 11:49 pm
Agree with dgermundson.
Depending on what you want to do with the output, one can also use a pivot table in Excel or a crosstab Crystal Reports which will make the SQL easier.
May 14, 2003 at 3:04 pm
quote:
You'll have to build a dynamic sql statement in a loop. Loop on the number of tests creating a select statement like this:declare @SqlClause as varchar(500)
select @SqlClause = 'select StudentId, '
--in cursor loop
fetch into @TestNameVariable
@SqlClause = @SqlClause + @TestNameVariable
fetch next...
--then execute the @SqlClause
exec( @SqlClause)
Darren
May 14, 2003 at 3:06 pm
Thanks for the help! I will use this guidance to try to solve the problem ... if you have time and wouldn't mind - I am not yet clear on how to create/name the cursor and populate the @TestNameVariable ... the beginning/surrounding code...
THX again.
quote:
You'll have to build a dynamic sql statement in a loop. Loop on the number of tests creating a select statement like this:declare @SqlClause as varchar(500)
select @SqlClause = 'select StudentId, '
--in cursor loop
fetch into @TestNameVariable
@SqlClause = @SqlClause + @TestNameVariable
fetch next...
--then execute the @SqlClause
exec( @SqlClause)
Darren
May 14, 2003 at 9:35 pm
You really dont have to loop although you do have to build dynamic sql.
Here is an example.
CREATE TABLE Tests (name varchar(50))
GO
INSERT INTO Tests
SELECT 'Test1'
GO
INSERT INTO Tests
SELECT 'Test2'
GO
INSERT INTO Tests
SELECT 'Test3'
GO
DECLARE @sql varchar(8000),
@separator varchar(10)
SELECT @sql = 'SELECT student_id,',
@separator = ''
SELECT @sql = @sql+@separator+name,
@separator = ','
FROM Tests
SELECT @sql = @sql + char(13)+char(10)+'FROM TABLE_NAME'
SELECT @sql
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply