How can I change the layout/structure of my table?

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.

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;