I have a table which holds numerical values (numerical answers to questions), and another table which contains text (verbatim answers to questions). I can easily link these tables to a table which holds customer information.
It's not a great structure, but it's what I have to work with.
Each row is one answer. Each question has an ID number and each survey has a serial number - these appear in both tables.
What I want to do is pivot the combined tables in a certain way.
For example, in the customer table I have one row for Mr Smith, SN 1234
To Question ID (QID) 5 he answers "10" and QID 11 he answers "2". For QID 5 leaves a comment of "hello world" (classic) and for QID 11 he leaves no comment.
In the 2nd table there are 2 rows, in the third table there is only one.
I want to arrange the data in such a way that the columns are:
Name | QID5 | QID5verbatim | QID11 | QID11verbatim
and a single row of data would be:
Mr Smith | 10 | Hello World | 2 | NULL
So basically, each QID row in each of the 2 answer tables is a column although slightly renamed.
I've managed to pivot the data with just the first 2 tables (customer info and numerical answers), but struggling on the final stage of this.