I have a DB with users table and an answers table. Each user has 30 answers. I want to export a user and all their answers on a single row.
e.g:
ID | email | answer_1 | answer_2 | answer_3 | etc...
----|---------|----------|----------|----------|--------
1 | [email protected] | foo | bar | baz | ....
I currently have
SELECT *
FROM users
LEFT JOIN answers AS A
ON users.id = A.user_id
WHERE email IS NOT NULL
This creates a 30 rows for each user with their answer against each one. How can I group this into one row per user with a column for each answer?
You can try using a pivot query, something like this:
SELECT uid, email,
MAX(CASE WHEN a.aid = 1 THEN a.answer END) AS answer_1,
MAX(CASE WHEN a.aid = 2 THEN a.answer END) AS answer_2,
MAX(CASE WHEN a.aid = 3 THEN a.answer END) AS answer_3
-- add more CASE expressions to cover all 30 answers
FROM users u
LEFT JOIN answers a
ON u.id = a.user_id
WHERE u.email IS NOT NULL
GROUP BY u.id, u.email
Assumptions: The answers table has an id column identifying and numbering each of the 30 answers. Without this, the query could be a real pain. I also assume that each user will have exactly 30 answers. Missing answers would show up as NULL in the case where they are not present.