Vertical table to Horizontal

[tcusolle]

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

[ck9663]

You can actually aggregate strings by using MIN().

Happy Coding!!!

~~ CK