Let’s say you are querying interview table for the interview id 488
Lets say we are only interested in these 3 disease specific fields
Lets get the interview values for the above fields along with the values from the interview table
The problem as you can see is that the interview data first 3 columns get repeated. Let’s use pivot clause to get the information in a single row.
Let’s create a Common Table Expression query to get all the data for the fields we are interested in from Interview Value table
Now lets Pivot Common Table expression table so that we can get an aggregate value like Count, sum, min, max etc., of field value for given field id.
The use of the square brackets [ ] around the column names in both the SELECT and the IN in the pivot clause is required.
These are keys, because the pivot operation is treating the values in these columns as column names.
Effectively you are grouping field values by two columns InterviewID and FieldID, then pivoting field id ie., you are treating the row values of field id as column names.
All we need to do now is to join our pivot to the interview table to get the required result.
That’s all folks!