How to write sql query to select records from this table

In my program I have generated a table like:

id Roll Subject     Marks LetterGrade GradePoint

1  1    Physics     40    D           1

2  1    Chemistry   50    C           2

3  1    Mathematics 60    B           3

4  2    Physics     40   开发者_运维知识库 D           1

5  2    Chemistry   50    C           2

6  2    Biology     59    C           2

I am using VB6 and MS Access as database. Now I want to show in Datagrid like:

Roll Sub     Marks LetterG Gp Sub        Marks LetterG Gp Sub            Marks LetterG  Gp GPA  GPAExcl

1    Physics 40    D       1  Chemistry  50    C       2  Mathematics    60    B        3  2.00 1.5

2    Physics 40    D       1  Chemistry  50    C       2  Biology        59    C        2  1.5  1.5

and more rows for more Roll

The subject with the greatest id for each roll is additional subject. If its Gp is greater than 2 then it is added with GPA, GPAExcl is only with first two subject

So for Roll 1 GPA is (1+2+1)/2=2.00 (Gp in Math is greater then 2 and 3-2=1)

and GPAExcl is (1+2)/2=1.5

and for Roll 2 both GPA and GPAExcl=1.5

How to write the sql query so I can get the records like this and can show it in datagrid

Is it possible to write such a query?

It seems to me that you need to use a DataList rather than a DataGrid. The DataList allows you to specify RepeatColumns and RepeatDirection.

In this case, it appears that you would want the RepeatDirection to be vertical. Then you just figure out how many columns you want.

I'm not sure you need the SQL to be any different. This seems to be a format issue--not a query issue.

OK, I think I was looking at that wrong. Sorry about that. I see now that you're wanting the Role for all three courses on the left, then all courses for that role on the right.

There are probably several ways to deal with this. One way would be to use two repeaters. This will create the table (you'll need to add the header row in). If you don't have the same amount of courses per role, you'll have to take care of columns via columnspan.

<asp:Repeater ID="repRoles" runat="server">
    <HeaderTemplate>
        <table>            
    </HeaderTemplate>
    <ItemTemplate>
        <tr>
            <td><%# Eval("Role") %></td>
            <asp:Repeater id="repCourses" runat="server">
                <ItemTemplate>
                    <td>
                        <%# Eval("Subject") %>
                    </td>
                    <td>
                        <%# Eval("Marks") %>
                    </td>
                    <td>
                        <%# Eval("LetterGrade") %>
                    </td>
                </ItemTemplate>
            </asp:Repeater>
        </tr>
    </ItemTemplate>
    <FooterTemplate>
        </table>
    </FooterTemplate>
</asp:Repeater>

You can use the ItemDataBound event on the first repeater to then bind the appropriate courses to the second repeater.