Hello, I’m a maths teacher who has started tracking my students’ exam results throughout the year in libreoffice base to see how they are going in different areas. Using the group and sum functions I can generate outputs along the lines of

student, area, mark, total

bill, trigonometry, 13, 15

bill, algebra, 12, 20

bill, statistics, 8, 12

sarah, trigonometry, 12, 15

sarah, algebra, 3, 20

sarah, statistics, 11, 12

However I can’t easily send this information out to my students via mail merge. Is there an easy way to write a query that will flatten the rows into columns, so the output is of the form:

bill, trigonometry, 13, 15, algebra, 12, 20, statistics, 8, 12

sarah, trigonometry, 12, 15, algebra, 8, 20, statistics, 7, 12

UPDATE

Thanks for the replies, sorry I could not update sooner. I’ve uploaded a copy of the database that shows how the data is structured. What I really want to generate is the same as the output of “qry_Syllabus_Summary” query. However I thought there must be a more effecient way of doing it than what I came up with. I think what I was really looking for was the ability to pivot, but as cpb stated that doesn’t seem to exist with HSQLDB, is it possible with firebird?