Need support for value_field to be a count(*) and not a column

Created by: paulperry

Maybe there is a way to do this, but it's not obvious at first. For example: I'd like to get a count of how many procedures were done to every patient at every hospital.

create table test (patient_id int, hospital_id int, procedure_id int);
insert into test (patient_id, hospital_id, procedure_id)
values 
(1, 100, A),
(1, 200, A),
(1, 300, B),
(1, 200, A),
(2, 100, C),
(2, 200, C),
(3, 100, A)
;

And get the following output:

patient_id | 100 | 200 | 300 
-----------+-----+-----+-----
1          | 1   | 2   | 1
2          | 1   | 1   | 0
3          | 1   | 0   | 0

My first thought was: select * from pivotmytable('test','pivotedtest','patient_id','hospital_id','procedure_id','count'); but the requirement that value_field be of type int does not make this possible. It would be useful if the count aggregator could count items that are not numeric.

Maybe if I use another query result to it, it might work? select patient_id, hospital_id, count(procedure_id) as procedure_count from test group by 1,2 order by 1,2;