Dynamic SQL / Pivot
I have a source database that manages flexible forms. What I mean by this is that there are some hard-coded fields required for all forms, (Work Group and Form Name for example) but every other field is customized by users. The fields are saved vertically in rows and since each form can have wildly different numbers of columns with different column names, I created a stored procedure that will iterate through the combinations of Work Group and Form Name to yield the right column list for each form. Essentially, when it runs, the idea is that it'll dynamically give the output from all forms. I've thought about enhancing the procedure to SELECT INTO tables for every combination of workgroup/form name and then insert into thereafter, but there are some inherent problems with that (namely if users change form fields). What I was hoping to do instead was use the dynamic SQL that pivots the data, giving the (currently) 50+ combinations of forms to put it into a view in YF. I tested it in a FH SQL report and it fails for executequery method since it's updating data. My question is there anything that could support the variable nature of the results in a view? As noted, every combination of forms would yield a different result set (some forms may have 4 columns whilst others may have 50). I know this is a long shot, but wanted to see if you might have a way to address this so I don't have to dynamically maintain dozens of automatically created tables when new forms are created.