How to turn groups of rows into separate columns?

Question

I have a postgresql table that looks like this:

a|b|c|result
0|3|6|50
0|3|7|51
0|4|6|52
0|4|7|53
1|3|6|54
1|3|7|55
1|4|6|56
1|4|7|57

Is there an easy way to SELECT something like:

a|result for b=3|result for b=4
0|sum(50,51)    |sum(52,53)
1|sum(54,55)    |sum(56,57)

In other words, how to convert the groups of values of b into columns of aggregate functions like sum(), avg(), or others?

Thanks for your comments.

I'd suggest a crosstab query: http://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905 — Erwin Brandstetter, Jun 25 '14 at 19:18

score 2 · Accepted Answer · answered Jun 25 '14 at 14:23

Not sure I understand your question completely, but I think you are looking for case.

-- drop table if exists sample;

create table sample
  (a int,
   b int,
   c int,
   result int);

insert into sample values
(0,3,6,50),
(0,3,7,51),
(0,4,6,52),
(0,4,7,53),
(1,3,6,54),
(1,3,7,55),
(1,4,6,56),
(1,4,7,57)
;

select
  a,
  sum(case when b = 3 then result end) as result_for_b3,
  sum(case when b = 4 then result end) as result_for_b4
from
  sample
group by
  a

Result:

a;result_for_b3;result_for_b4
1;109;113
0;101;105

And if you (but I hope you don't) need to have output exactly as in your question, than you need to use string_agg function:

select
  a,
  'aggreg(' || string_agg(case when b = 3 then result end::varchar, ',') || ')' as result_for_b3,
  'aggreg(' || string_agg(case when b = 4 then result end::varchar, ',') || ')' as result_for_b4
from
  sample
group by
  a

Result:

a;result_for_b3;result_for_b4
0;aggreg(50,51);aggreg(52,53)
1;aggreg(54,55);aggreg(56,57)

Thank you Tomas! your straightforward answer made my day. – Fabio A. Correa Jun 25 '14 at 14:37 — Fabio A. Correa, Jun 25 '14 at 14:37

How to turn groups of rows into separate columns?

1 Answers1