|There's more than one way to do things|
Just to refocus on the OP, the question was, (given the example 'one-off' solution that BrowserUk illustrated for this specific example, and considering the similarly-coded 'one-off' solutions by the OP in the past) where does there exist a "general purpose" mechanism to further abstract the process of doing this kind of transformation, such that there is no need to modify the 'one-off' code every time someone wants a different 'pivot' of the data structure.
The question is now mostly academic, since the humble aspirant has gone on to code a solution himself, absent a preexisting and obvious choice out there.
It is called 'vpath_pivot', named after the 'pivot table' feature found in spreadsheet software. It generates arbitrarily-nested tree structures using perl data variables, constructed from queries like:
NOTE: the above is not SQL, it just looks similar for readability.
NOTE: the main approach here is to give the user the ability to 'slice and dice' the 'flat' data into different 'tree structures' all within a simple syntax (i.e., no need to re-edit the perl code).tyvm for the replies
In reply to Re^3: tree-oriented query against table-oriented data