get 2 columns in my query result

问题:

I have a table that looks like the following

PIN    QuestionNum     Response
1111   1               1
1111   2               3
2222   1               4
2222   2               3
3333   2               5

The expected output from my query would be:

PIN   Question1   Question2
1111  1           3
2222  4           3
3333  null        5

I don't know if it is possible to make the output of a query appear like this.

Could anyone please advise if this is possible please.

回答1:

This is basically a PIVOT but MySQL does not have PIVOT function. So you will want to replicate this using an aggregate function and a CASE statement. If you know the number of QuestionNum values that you have then you can hard-code the query similar to this:

select pin,
  max(case when QuestionNum = 1 then response end) Question1,
  max(case when QuestionNum = 2 then response end) Question2
from yourtable
group by pin

See SQL Fiddle with Demo

Now, if you have an unknown number of values for QuestionNum, then you can use a prepared statement to generate a dynamic version of this query:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when QuestionNum = ''',
      QuestionNum,
      ''' then response end) AS Question',
      QuestionNum
    )
  ) INTO @sql
FROM yourtable;

SET @sql = CONCAT('SELECT pin, ', @sql, ' 
                  FROM yourtable 
                  GROUP BY pin');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

Both will produce the same results:

|  PIN | QUESTION1 | QUESTION2 |
--------------------------------
| 1111 |         1 |         3 |
| 2222 |         4 |         3 |
| 3333 |    (null) |         5 |