Split two rows into two columns

Question

I have the following table:

id   | name   | action | count
------------------------------
 1   | Value1 |    0   | 27
 1   | Value1 |    1   | 49
 2   | Value2 |    0   | 7
 2   | Value2 |    1   | 129
 3   | Value3 |    0   | 9
 3   | Value3 |    1   | 7

I need to make the 'action' column appear twice, with the count value of each line in it, something like this:

id   | name   | action1 | action2
---------------------------------
 1   | Value1 |    27   | 49
 2   | Value2 |    7    | 129
 3   | Value3 |    9    | 7

How can I do this? Here's my script:

SELECT m.id,
    t.name,
    m.action,
    count(m.id) AS count
FROM table1 m
LEFT JOIN table2 t ON (m.id = t.id)
WHERE m.status != '2'
GROUP BY m.id,
    t.name,
    m.action
ORDER BY 1, 3

Answer 1

You can use a conditional aggregate:

select m.id, 
       t.name, 
       count(case when m.action = 0 then m.id end) as action1,
       count(case when m.action = 1 then m.id end) as action2
from table1 m
  left table2 t ON (m.id = t.id)
where m.status <> '2'
group by m.id, t.name;

In the upcoming 9.4 release the count(case(...)) could be written a bit shorter using the new "filtered" aggregate: count(id) filter (when m.action = 0)