How to get distinct values relationizing splited column values in SQL

user_11023255 0
Viewed 28

I have a table like the example below.

+--------+--------+
|  name  | answer |
+--------+--------+
| John   | yes    |
| Dilan  | yes    |
| Dilan  | no     |
| Amanda | no     |
+--------+--------+

I need to reorganize the table in a way that I have distinct values in name column and the answer column split in two columns, the answer_yes and answer_no, matching the related answers from the original table.

+--------+------------+-----------+
|  name  | answer_yes | answer_no |
+--------+------------+-----------+
| John   | yes        |           |
| Dilan  | yes        | no        |
| Amanda |            | no        |
+--------+------------+-----------+

I am guessing I have to use some function that search for the values yes and no through the answer column, but I didn't find what function does this work... how can I make it?

aggregate-functions pivot count sql
2 Answers

Use aggregation:

select name,
    max(case when answer = 'yes' then 'yes' end) answer_yes,
    max(case when answer = 'no'  then 'no'  end) answer_no
from mytable
group by name

If a name may have more than one "yes" or "no", it would be more useful to count the values:

select name,
    sum(case when answer = 'yes' then 1 else 0 end) cnt_answer_yes,
    sum(case when answer = 'no'  then 1 else 0 end) cnt_answer_no
from mytable
group by name
user_106767160

Just use conditional aggregation:

select name,
       max(case when answer = 'yes' then answer end) as answer_yes,
       max(case when answer = 'no' then answer end) as answer_no
from t
group by name;
user_11440350
Related