jtornero / pivotmytable Goto Github PK

Hello, would you consider changing the license to MIT or APACHE 2?

Pivotmytable function uses 'information_schema' as source to get details about tables and columns but this schema doesn't contain information about materialized views. One should use Postgresql specific catalog tables (e.g. pg_attribute) which are source of information on all relations (tables, views, materialized views, etc...).
The SQL script can be modified according to Erwin Brandstetter advices as described on http://stackoverflow.com/questions/19127561/how-to-introspect-materialized-views.

Hi I love this function although I had a difficult time getting it working for some tables initially.

I had to make the following change to the program to get it working :

            #fieldType = ("numeric(%i, %i)" %(numericFieldWidth, columnScale))
            fieldType = ("double precision")

I am wondering now how I can get NULL values to show up?

    # This trick makes possible to get rid of the null values in the pivoted tables.
    # TODO: Consider to make it optional, with a parameter for it.

    replaceZeros = ['coalesce("{0}",0) as "{0}"'.format(destColumn["columns"]) for destColumn in destColumns]
    replaze0 = ','.join(replaceZeros)

What should I do here to make null values show up instead of '0'

Thanks for the great function and any help.

Next project for me is to pivot varchar fields.

This would be great ... getting hung up on the agg functions

I've installed the pivotmytable into my DB instance, and I've created the example myinfo table, but for some reason I can't execute the query:

# select * from pivotmytable('myinfo','pivotedinfo','player,tool','round','sum');
ERROR:  function pivotmytable(unknown, unknown, unknown, unknown, unknown) does not exist
LINE 1: select * from pivotmytable('myinfo','pivotedinfo','player,to...
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Do you have any ideas about how to debug this?

Hi,

I have been using this script for quite a while without any issue. But suddenly this script stopped working and whenever I run this select * from pivotmytable query my postgreSQL database service stops and I am not able create the pivot table. My original table almost has 1 million records, could this be the reason for this issue? Kindly confirm.

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;