CODEWITHSUNDEEP
mysqlpivot
kylex
kylex

Reputation: 14416

Reshape data with MySQL

I have a table constructed like so:

+----+---------+---------+----+----+
| id | filter1 | filter2 | q1 | q2 |
+----+---------+---------+----+----+
|  1 | america | y       |  1 |  2 |
|  2 | asia    | y       |  4 |  3 |
+----+---------+---------+----+----+

What I need is to pivot(?) on q1 and q2 under the same heading question, and the row data in a new column called answers

The final table would look like:

+----+---------+---------+----------+--------+
| id | filter1 | filter2 | question | answer |
+----+---------+---------+----------+--------+
|  1 | america | y       | q1       |      1 |
|  1 | america | y       | q2       |      2 |
|  2 | asia    | y       | q1       |      4 |
|  2 | asia    | y       | q2       |      3 |
+----+---------+---------+----------+--------+

Upvotes: 0

Views: 146

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

The easiest way is with union all:

select id, filter1, filter2, 'q1' as question, q1 as answer
from table t
union all
select id, filter1, filter2, 'q2' as question, q2 as answer
from table t;

If you have really large tables or more questions, then this might not be the most efficient approach.

EDIT:

For a huge table, this might be more efficient:

select id, filter1, filter2,
       (case when n.n = 1 then 'q1' else 'q2' end) as question,
       (case when n.n = 1 then q1 else q2 end) as answer
from table t cross join
     (select 1 as n union all select 2 as n) n;

This should do a full table scan of the table only once rather than once for each subquery in the union all.

Upvotes: 1

Related Questions