<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource2" BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" CellPadding="5" ForeColor="Black" GridLines="Horizontal">
            <Columns>
                <asp:TemplateField>
                    <ItemTemplate>
                        <%# Container.DataItemIndex + 1 %>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField DataField="student_name" HeaderText="Name" SortExpression="student_name" />
                <asp:BoundField DataField="student_number" HeaderText="Student ID" SortExpression="student_number" />
                <asp:BoundField DataField="student_email" HeaderText="Student Email" SortExpression="student_email" />
                <asp:BoundField DataField="course_name" HeaderText="Course" SortExpression="course_name" />
 
            </Columns>
            <FooterStyle BackColor="#CCCCCC" />
            <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="#CCCCCC" />
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:markingConnectionString %>"
            SelectCommand="SELECT     subject_id, student_id, student_name, student_number, student_email, course_name, 
                        Max((CASE WHEN RowNumber = 1 THEN total ELSE '' END)) AS hurdle1, 
                        Max((CASE WHEN RowNumber = 2 THEN total ELSE '' END)) AS hurdle2, 
                        Max((CASE WHEN RowNumber = 3 THEN total ELSE '' END)) AS hurdle3, 
                        Max((CASE WHEN RowNumber = 4 THEN total ELSE '' END)) AS hurdle4, 
                        Max((CASE WHEN RowNumber = 5 THEN total ELSE '' END)) AS hurdle5
FROM         (SELECT     Student.student_id, Student.student_name, Student.student_number, Course.course_name, Student.student_email, Subject.subject_id, 
                                              ROW_NUMBER() OVER (PARTITION BY Student.student_id
                       ORDER BY Student.student_id) AS RowNumber, SUM(Marks.marks_value) * Hurdle.hurdle_weight / 100 AS total
FROM         Marks INNER JOIN
                      Student ON Marks.student_id = Student.student_id INNER JOIN
                      Class ON Student.student_id = Class.student_id INNER JOIN
                      Subject ON Class.subject_id = Subject.subject_id INNER JOIN
                      Course ON Student.course_id = Course.course_id INNER JOIN
                      MarkingCriteria ON Marks.criteria_id = MarkingCriteria.criteria_id INNER JOIN
                      AssessmentComponent ON MarkingCriteria.component_id = AssessmentComponent.component_id INNER JOIN
                      Hurdle ON AssessmentComponent.hurdle_id = Hurdle.hurdle_id
GROUP BY Hurdle.hurdle_id, Hurdle.hurdle_weight, Student.student_id, Student.student_name, Student.student_number, Student.student_email, 
                      Course.course_name, Subject.subject_id) AS derive
GROUP BY subject_id, student_id, student_name, student_number, student_email, course_name" FilterExpression="subject_id = '{0}'">
            <FilterParameters>
                <asp:ControlParameter Name="Subject" ControlID="dropdown_subjectCode" PropertyName="SelectedValue" />
            </FilterParameters>
        </asp:SqlDataSource>

 
        string selectedValue = dropdown_subjectCode.SelectedValue.ToString();
        GridView1.Columns.Clear();
 
        GridView1.DataBind();
 
        BoundField boundField = new BoundField();
        boundField.DataField = "student_name";
        boundField.HeaderText = "Name";
        GridView1.Columns.Add(boundField);
 
        boundField = new BoundField();
        boundField.DataField = "student_number";
        boundField.HeaderText = "Student ID";
        GridView1.Columns.Add(boundField);
 
        boundField = new BoundField();
        boundField.DataField = "student_email";
        boundField.HeaderText = "Student Email";
        GridView1.Columns.Add(boundField);
 
        boundField = new BoundField();
        boundField.DataField = "course_name";
        boundField.HeaderText = "Course";
        GridView1.Columns.Add(boundField);
 
        for (int i = 0; i < db.getHurdleCount(dropdown_subjectCode.SelectedValue.ToString()); i++)
        {
            BoundField bf = new BoundField();
            string hurdleField = "hurdle" + (i + 1);
            bf.DataField = hurdleField;
            bf.HeaderText = "Hurdle " + (i + 1) + " Marks";
            GridView1.Columns.Add(bf);
        }
 
        if (db.getHurdleCount(dropdown_subjectCode.SelectedValue.ToString()) > 0)
        {
            BoundField bf = new BoundField();
            bf.DataField = "";
            bf.HeaderText = "Total Marks";
            GridView1.Columns.Add(bf);
 
            bf = new BoundField();
            bf.DataField = "";
            bf.HeaderText = "Grade";
            GridView1.Columns.Add(bf);
        }