I currently have a table with 3 columns in it. The columns are best described as group_id, task and task_count. There are up to 15 possible tasks and over 500,000 group_ids. The task_count is the number of events that took place in a group_id for that task. Currently, the table looks like this:
group_id task_count task 5555 45 A 5555 4 N 5624 67 A 5624 23 O 5624 42 X
So in group 5555 we only have 2 tasks: A which was done 45 times and N which was done 4 times. In 5624 we have 3 tasks with their respective counts. What I would like to do is put these values to where it displays based on the group id. So that it would look like:
group_id TASK_A TASK_N TASK_O TASK_X 5555 45 4 0 0 5624 67 0 23 42
Pleae note that I’d like to incorporate the task value in the column name instead of ‘task_count’. What is the best way to convert it into the above format? Thank you.
Advertisement
Answer
You can use conditional aggregation:
select group_id,
sum(case when task = 'A' then task_count else 0 end) as a,
sum(case when task = 'N' then task_count else 0 end) as n,
sum(case when task = 'O' then task_count else 0 end) as o,
sum(case when task = 'X' then task_count else 0 end) as x
from t
group by group_id;
Presumably, your original table is built from some unsummarized base table. You can apply this directly to that table:
select group_id,
sum(case when task = 'A' then 1 else 0 end) as a,
sum(case when task = 'N' then 1 else 0 end) as n,
sum(case when task = 'O' then 1 else 0 end) as o,
sum(case when task = 'X' then 1 else 0 end) as x
from base
group by group_id;