Overview of the problem:
The goal of the apex app is to provide a way of filling out a checklist more or less for various items in production, each production item has a list of criteria it needs to meet. The customer wants to be able to export the current progress of the checklist to a CSV at any given time, but in a particular format.
The format required is a grid where each row header is the name of a criteria that needs to be completed in the checklist, and the column headers are the names of the items for which the checklist needs to be completed on. In the cell where a part and criteria intersect, there needs to be a response (Yes, No, N/A...)
The issue I am running into comes from the fact that the amount of parts that need to be on each exported csv varies by quite a bit and I was hoping to be able to use the "Download" function that is built into apex's Interactive Reports so that users can see what the CSV will look like before it is exported.
What I have tried:
I have considered using a collection for this but the amount of parts (columns) in the exported CSV can exceed 50. I suppose I could join many of these together but no matter how many I join there would still be an upper limit on the amount of parts in the CSV, which is undesirable.
I have also looked at Global Temporary Tables a bit but I would like whatever object stores this data to be specific to a user's session so I don't think this could be a possibility either.
I looked at materialized views as well but I don't believe the format that the customer requires is possible to create using a static SQL query but I could be wrong.
Any other solution or an application of one of the solutions I have listed that can overcome the shortcomings I laid out would be greatly appreciated.
This is how I would tackle this:
PIVOT to generate the columns on the fly. PIVOT pretty complicated but there are a lot of sources. Have a look at this excellent blog by Chris Saxon, but there are many other sources.