Need help with a query/procedure

  • 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.







    -- *** Test Data ***

    CREATE TABLE dbo.Students


        StudentID int NOT NULL

            CONSTRAINT PK_Students PRIMARY KEY

        ,StudentName varchar(20) NOT NULL



    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)



    INSERT INTO dbo.Students

    SELECT 1, 'Archie' 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 ***


    -- Function to concatenate grades

    CREATE FUNCTION dbo.GetStudentGrades


        @StudentID int


    RETURNS varchar(500) -- or whatever is a reasonable length



        DECLARE @Result varchar(500)

        SET @Result = ''

        SELECT @Result = @Result + Grade + ','

        FROM dbo.StudentGrades

        WHERE StudentID = @StudentID


            CASE LEN(@Result)

                WHEN 0 THEN @Result

                ELSE LEFT(@Result, LEN(@Result) - 1)




    -- *** Now do the query ***

    SELECT *

        ,dbo.GetStudentGrades(StudentId) AS Grades

    FROM dbo.Students


  • 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.

  • Thank you! Ken that worked .

  • Thank You!

  • 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.


  • 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..




  • 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