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

[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;

[jtornero]

@paulperry Could you please add a little example of you want to get? I'll try to do my best (but it's not likely to be in the inmediate future, sorry)

Cheers,

Jorge

[paulperry]

Jorge: Thanks for replying. I had no way to reach you. I've updated the comment with a small example of what I'm trying to do.

On Mon, Jan 18, 2016 at 5:29 AM, Jorge Tornero [email protected] wrote:

@paulperry https://github.com/paulperry Could you please add a little example of you want to get? I'll try to do my best (but it's not likely to be in the inmediate future, sorry)

Cheers,

Jorge

— Reply to this email directly or view it on GitHub https://github.com/jtornero/pivotmytable/issues/6#issuecomment-172491714 .

[jtornero]

@paulperry At first glance looks like that there is some trouble when trying to guess the column type. Must check.

Cheers

Jorge

[jtornero]

@paulperry Looks like it needs to let non-numeric columns play if the agg_func is count. For a workaround, add after line 179, indenting as appropiate

    elif agg_func=='count':
        fieldType="integer"

I'll try to test it thoroughly and fix the issue as soon as I can.

Cheers

Jorge

[paulperry]

@jtornero Yes, that works for me. Thanks!