Base flatten query

[mathteacher]

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?

Flatten_Query.odb

[igorlius]

Can you show us your db schema, That might make it easier to construct an appropriate query.

[cpb]

@mathteacher asked Is there an easy way to write a query that will flatten the rows into columns?

none of the open source databases can pivot data to this extent, most cannot pivot at all,
however any open source database can be used to achieve your desired result by using what is known as conditional aggregation.

you say that your data is sourced via a query but do not show that query nor have you disclosed the database you use which makes it impossible to provide a bespoke solution so i see no point in pasting code here.

i have uploaded an example of conditional aggregation using hsqldb embedded, it consists of 1 table containing your data and 1 query which probably looks like chinese to you.

if you study the code you will find that it’s quite logical, i have included comments which will hopefully help.

a query of this type is not dynamic, limits are hard coded and it’s vital that the code caters for the maximum possible number of (in this case) “areas” per student.

EDIT: 16 Sep 20.
attachment replaced.
i was in the shower when i realised that i had over complicated things.

Maths.odb

[cpb]

it seems that the OP will not respond which helps no one and means that the thread cannot develop.

my solution was based on the default database ‘HSQLDB 1.8.0.10’.

i suggest that anyone using LibreOffice Base with ‘HSQLDB Embedded’ and wishing to continue using HSQLDB should split their database and upgrade to HSQLDB 2.x else use any other mature relational database.

upgrading will bring stability and enable access to a host of additional functions including the ability to use date/time arithmetic.

a HSQLDB 2.x solution to this thread is:

select
	"student" || ', ' ||
	group_concat("area" || ', ' || "mark" || ', ' || "total" order by "area" separator ', ') "result"
from
	(select "student", "area", "mark", "total" from "tMaths")
group by "student"
order by "student"

all open source relational databases have a similar function to HSQLDBs GROUP_CONCAT().

i will not be making any further posts on this site. bye.

[mathteacher]

Hi cpb, conditional aggregation was what I needed. Thanks for your help.

[Ratslinger]

Hello,

There is also another option. This does require using macro coding but you can have mail merge execute as the reports are generated.

In Base use Report Builder to create a report by student. Then you will need to adopt the code in this post How to print a base report and send it by mail (mailmerge).

This does require some understanding of macros. Just presenting as an option.

[mathteacher]

Hi Ratslinger, thanks for this option. I got the result I needed with conditional aggregation. Learning macro coding is on the to-do list, but it is a bit beyond me at the moment.