CODEWITHSUNDEEP
sqlamazon-redshift
wizkids121
wizkids121

Reputation: 656

Querying data to one row

I have some data structured as such:

+-----------+-------------+--------+
|    id     | question_id | answer |
+-----------+-------------+--------+
| 101827402 |         123 | All    |
| 101827402 |         456 | Yes    |
| 101827408 |         123 | All    |
| 101827408 |         456 | No     |
+-----------+-------------+--------+

I am trying to fit the data into one row based on the id field with each corresponding answer being one line. So the output I want would look as such:

+-----------+---------+---------+
|    id     | answer1 | answer2 |
+-----------+---------+---------+
| 101827402 | All     | Yes     |
| 101827408 | All     | No      |
+-----------+---------+---------+

I've tried running the following query, but it does not produce the result I'm looking for:

SELECT id, 
CASE WHEN question_id = 123 THEN answer END answer1, 
CASE WHEN question_id = 456 THEN answer END answer2 
FROM 
test_data

It returns results that look as such:

+-----------+---------+---------+
|    id     | answer1 | answer2 |
+-----------+---------+---------+
| 101827402 | All     | NA      |
| 101827402 | NA      | Yes     |
| 101827408 | All     | NA      |
| 101827408 | NA      | No      |
+-----------+---------+---------+

How would I get it to the output I want where it matches the second table posted here?

Upvotes: 1

Views: 29

Answers (1)

Sergey Geron
Sergey Geron

Reputation: 10152

Try to envelope it with aggregation:

SELECT id, 
MAX(CASE WHEN question_id = 123 THEN answer END) answer1, 
MAX(CASE WHEN question_id = 456 THEN answer END) answer2 
FROM test_data
GROUP BY id

Upvotes: 1

Related Questions