I have a set of data that represents how people ranked various traits. I am trying to Pivot the data by two columns however, the column data is contained under a single column with a separate column acting as a discriminator.
Here is the scenario. People were given a list of three traits and asked to rank them in order of importance to them. They were given two lists of three traits each and asked to rank each list 1 to 3, from most important to least important.
+----------+-------+----------------+---------------+------+
| RecordNo | Name | QuestionNumber | QuestionGroup | Rank |
+----------+-------+----------------+---------------+------+
| 1 | Bob | 1 | 1 | 2 |
| 2 | Bob | 2 | 1 | 1 |
| 3 | Bob | 3 | 1 | 3 |
| 4 | Bob | 1 | 2 | 1 |
| 5 | Bob | 2 | 2 | 2 |
| 6 | Bob | 3 | 2 | 3 |
| 7 | Sally | 1 | 1 | 3 |
| 8 | Sally | 2 | 1 | 2 |
| 9 | Sally | 3 | 1 | 1 |
| 10 | Sally | 1 | 2 | 1 |
| 11 | Sally | 2 | 2 | 3 |
| 12 | Sally | 3 | 2 | 2 |
+----------+-------+----------------+---------------+------+
What I would like to end up with is a PIVOT of the data so it looks like this ..
+----------+-------+-----------+-----------+-----------+------------+------------+------------+
| RecordNo | Name | Question1 | Question2 | Question3 | Question 1 | Question 2 | Question 3 |
| | | Group 1 | Group 1 | Group 1 | Group 2 | Group 2 | Group 2 |
+----------+-------+-----------+-----------+-----------+------------+------------+------------+
| 1 | Bob | 2 | 1 | 3 | 1 | 2 | 3 |
| 2 | Sally | 3 | 2 | 1 | 1 | 3 | 2 |
+----------+-------+-----------+-----------+-----------+------------+------------+------------+
I know how to do a Pivot on multiple columns great article on it here but what I cannot figure out is how to Pivot when the data is in the same column (QuestionNumber) separated by a discriminator column (QuestionGroup).
I also created an online table here
I think the simplest method for pivoting is conditional aggregation:
select name,
max(case when questiongroup = 1 and questionnumber = 1 then rank end) as q_1_1,
max(case when questiongroup = 1 and questionnumber = 2 then rank end) as q_1_2,
max(case when questiongroup = 1 and questionnumber = 3 then rank end) as q_1_3,
max(case when questiongroup = 2 and questionnumber = 1 then rank end) as q_2_1,
max(case when questiongroup = 2 and questionnumber = 2 then rank end) as q_2_2,
max(case when questiongroup = 2 and questionnumber = 3 then rank end) as q_2_3
from t
group by name;