I have a page that dynamically builds forms input fields from a table allowing users to enter input into the form. Each input is a field in a _info table.
I would like report showing all of the entries of a user in one line. Each field entry is a row. So I need to take the vertical data and make it horizonal. There are many different forms with different configurations so must be dynamic. I’ve come across a lot of articles for cross-tab pivots but they only work for aggragates/sums.
The Registration_Info table has multiple entries for one form submission. I need to list these horazontal with each form_field.label as its fieldname for reporting.
Below is an example table layout:
FORM
——————————————
FORM_ID|FORM_NAME
500|“Custom Form 1″
FORM_FIELD
——————————————
FORM_FIELD_ID|FORM_ID|LABEL
1|500|“Field 1″
2|500|“Field 2″
REGISTRATION
——————————————
REG_ID|FORM_ID|USER_ID
23|500|45
24|500|98
REGISTRATION_INFO
——————————————
REG_ID|FORM_FIELD_ID|FIELD_VALUE
23|1|“My Name”
24|2|“My City”
Required Report Output:
REG_ID |Field 1 |Field 2 |FORM_ID |USER_ID
———————————————————————————————
23 |My Name |My City |500 |45
24 |Another Name |Another City |500 |98