Using @Kabirdas' great help here, I have this now working. I'm not sure if it's the most efficient (yet) as it's three SQL queries, but it works very nicely.
- Query Table (queryTable)
SELECT * FROM Table
- Convert Data to Array (tableToArray)
SELECT * FROM {{formatDataAsArray(queryTable.data)}}
- Convert Array to Array of objects (ticketArrayObjects)
SELECT Ticket ,array(@{Task: Task, Description: Description}) as Tasks
FROM {{tableToArray.data}} GROUP BY Ticket
Query result looks like this
Ticket (the group by) value can be accessed at
{{ticketArrayObjects.data[ri[0]].Ticket}}
The individual values (Task, and Description) can be accessed at
{{ transformedTicketstoArray.data[ri[0]].Tasks[ri[1]].Task }}
{{ transformedTicketstoArray.data[ri[0]].Tasks[ri[1]].Description }}
Gives something like this (not formatted yet)
