February 23, 2007 at 8:51 am
I am a newbie to sql....
Data In general looks like this ( there could be more rows for each student no.):
Student Grade
1 A
1 B
2 C
2 D
3 E
3 F
Needs to be presented as given below grouped by Student no.
Student Grade
1 A,B
2 C,D
3 E,F
Can anyone please help me with writing a query or procedure to do this.
February 23, 2007 at 10:18 am
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- *** Test Data ***
CREATE TABLE dbo.Students
(
StudentID int NOT NULL
CONSTRAINT PK_Students PRIMARY KEY
,StudentName varchar(20) NOT NULL
)
GO
CREATE TABLE dbo.StudentGrades
(
StudentID int NOT NULL
CONSTRAINT FK_StudentGrades_Students REFERENCES dbo.Students(StudentID)
,ExamID int NOT NULL
-- CONSTRAINT FK_StudentGrades_Exams REFERENCES dbo.Exams(ExamID)
,Grade char(1) NOT NULL
CONSTRAINT CK_StudentGrades_Grade CHECK (Grade LIKE '[A-F]')
,CONSTRAINT PK_StudentGrades PRIMARY KEY (StudentID, ExamID)
)
GO
INSERT INTO dbo.Students
SELECT 1, 'Archie' UNION ALL
SELECT 2, 'Bob' UNION ALL
SELECT 3, 'Charlie'
INSERT INTO dbo.StudentGrades
SELECT 1, 1, 'A' UNION ALL
SELECT 1, 2, 'B' UNION ALL
SELECT 2, 1, 'C' UNION ALL
SELECT 2, 2, 'D' UNION ALL
SELECT 3, 1, 'E' UNION ALL
SELECT 3, 2, 'F'
-- *** End Test Data ***
GO
-- Function to concatenate grades
CREATE FUNCTION dbo.GetStudentGrades
(
@StudentID int
)
RETURNS varchar(500) -- or whatever is a reasonable length
AS
BEGIN
DECLARE @Result varchar(500)
SET @Result = ''
SELECT @Result = @Result + Grade + ','
FROM dbo.StudentGrades
WHERE StudentID = @StudentID
RETURN
CASE LEN(@Result)
WHEN 0 THEN @Result
ELSE LEFT(@Result, LEN(@Result) - 1)
END
END
GO
-- *** Now do the query ***
SELECT *
,dbo.GetStudentGrades(StudentId) AS Grades
FROM dbo.Students
February 23, 2007 at 10:18 am
Do you know how many grades a student has? Is it consistent?
If not, you'd be better off doing this in the client somewhere. Loop through the records and display the next grade until the student changes.
February 23, 2007 at 11:10 am
Thank you! Ken that worked .
February 23, 2007 at 11:14 am
Thank You!
February 23, 2007 at 11:14 am
The Students and grades was just an example to explain it.
Yes, the no. grades for a student are consistent. I needed to take the end result and then insert it in different table.
March 16, 2007 at 11:10 am
Based on the help with the query I was trying to create a procedure/function as given below
Before I started to write the procedure or a function, I started doing what is given below.
--Pass the subject ids,class id, student id in to the procedure or a function.
Declare @vrsubjectIds As Varchar(100)
Declare @intClassid int
Declare @vrStudentid varchar(10)
Declare @sql As varchar(3000)
Declare @Result As varchar(500)
Set @intClassid =1
Set @vrStudentid ='0001'
Set @vrsubjectIds='17,20,34'
SET @Result = ''
Set @sql= 'SELECT @Result = @Result + Grade + ' + char(39) + char(44) +char(39) + ' As Grades FROM StudentGrades WHERE ClassID=' + ltrim(str(@intClassid)) + ' and studentid=' + @vrStudentid +
' And Subject_Id in (' + @vrsubjectIds + ')'
exec (@sql)
When I run the above mentioned, I get an error as given below... I know that the @Result variable has been declared. Is it is because I am using it as part of dynamic sql..
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@Result'.
Can some please help me with this to fix it.
Thanks in advance..
March 19, 2007 at 5:09 am
Instead of using a comma delimited list of subject IDs, and dynamic SQL, try re-writing the query with static SQL and a sub-query in the IN statement.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply