I have a bit of an interesting dilemma that I could use some pointers on. I can build whatever might be needed in a public Apex WS but figured this was more of a SQL theory question. For some context, we are converting an existing MS Access application / SQL Server back end to Apex. That application currently handles mail for mailbox email addresses, creating tickets (around 300 or so unique email addresses/boxes).
In the Apex replacement, we have a standard tickets report / search page that is used for everything ticket-related. We have given each box the ability to have 0 to (technically) unlimited additional custom data fields depending on their team's needs in tracking tickets, with the details for each box's fields and each ticket's values for those fields stored in several separate tables. Here are the objects in question, simplified:
TICKETS Table
TICKET_ID / BOX_ID / OTHER_COLS
FIELDS Table
FIELD_ID / BOX_ID / FIELD_LABEL
VALUES Table
TICKET_ID / FIELD_ID / FIELD_VALUE
We additionally have a view used on the main display for a ticket that pulls back a ticket's field values, which should help this question.
FIELD_VALUES View
TICKET_ID / FIELD_ID / FIELD_LABEL / FIELD_VALUE
For example, a ticket in a box with 3 fields would have 3 lines in this view, one for each of the fields and any stored value for them. A box that has no fields built at all would see no tickets, as this view expects at least one FIELD_ID for every ticket. This is fine since they can just use the normal ticket report / search mentioned previously.
We need to generate an additional report which contains all the existing ticket data from the base level report, plus all of the box's fields (with the columns displayed taking on the label for that field) and the values for each ticket. Because of the differences in the number of fields between boxes, I've limited it to only one box at a time for the ticket fields report.
I reckoned the easiest way to do this was to query ALL of the box's saved fields against all of the tickets to get the below sample. This would mean they could show/hide them in the IR, as opposed to letting the cust pick what goes into the report in the first place. However, I have no clue how to do it. I found some details about building a function related to it but they were hidden behind a zip file I can't download . This would be an example of desired output:
(limited to Box 1)
TICKET_ID - #LABEL1# - #LABEL2# - etc
1 / ValueSample / WompWomp
2 / Example / NULL
Previously we'd attempted to achieve it with left joins limited to only 5 fields, as well as with CROSS JOINs and attempts at PIVOTs. For now we're back to the 5 field report which doesn't run well. We will be handling millions of emails and thousands of tickets annually, and need something that can at least handle SOME of that scale, I reckon not every box will even have fields to begin with, so total user base needing this specific report is somewhat narrow. I was hoping the forums could help me out on what to do! I'm familiar with JavaScript, but not XML, which also showed up in my researching. If either of those are better solutions. Let me know if I can provide further details or anything!