The postgreSQL crosstab function is really powerful

Posted on by

I’m just beginning to wrap my brain around it. I’ve got some data that looks like this:

select rr as row_name, dt, c from mm limit 3;
+----------+----------+------+
| row_name | dt | c |
+----------+----------+------+
| xxx | 2009 mar | 552 |
| xxx | 2009 nov | 2179 |
| xxx | 2009 jun | 1101 |
+----------+----------+------+
(3 rows)

And now I’m going to transpose it like this:

select * from crosstab('select rr as row_name, dt, c from mm limit 3')
as foo(row_name text, dt_1 bigint, dt_2 bigint);
+----------+------+------+
| row_name | dt_1 | dt_2 |
+----------+------+------+
| xxx | 552 | 2179 |
+----------+------+------+
(1 row)

Neat, right?

One thought on “The postgreSQL crosstab function is really powerful”

  1. Thanks for the comment on crosstab. I love this functionality and have used it quite a bit but I am encountering a puzzling duplication of row headers. I have created the following small example that illustrates the problem:

    CREATE TABLE day10
    (
    userid double precision,
    sType integer
    );
    INSERT INTO day10(userid, sType) VALUES(1234, 0);
    INSERT INTO day10(userid, sType) VALUES(1234, 0);
    INSERT INTO day10(userid, sType) VALUES(1234, 0);
    INSERT INTO day10(userid, sType) VALUES(1234, 0);
    INSERT INTO day10(userid, sType) VALUES(1234, 1);
    INSERT INTO day10(userid, sType) VALUES(1234, 0);
    INSERT INTO day10(userid, sType) VALUES(1234, 0);
    INSERT INTO day10(userid, sType) VALUES(4321, 0);
    INSERT INTO day10(userid, sType) VALUES(4321, 1);
    INSERT INTO day10(userid, sType) VALUES(4321, 0);
    INSERT INTO day10(userid, sType) VALUES(4321, 0);
    INSERT INTO day10(userid, sType) VALUES(4321, 0);
    INSERT INTO day10(userid, sType) VALUES(4321, 1);
    INSERT INTO day10(userid, sType) VALUES(4321, 0);
    INSERT INTO day10(userid, sType) VALUES(4321, 0);
    INSERT INTO day10(userid, sType) VALUES(4321, 0);

    SELECT sTypereport.* FROM crosstab('SELECT userid, sType, count(*) as counts from day10 GROUP BY userid, sType;', 'select * from generate_series(0,2);') As sTypereport(userid double precision, type1 integer, type2 integer, type3 integer);

    userid | type1 | type2 | type3
    ——–+——-+——-+——-
    1234 | 6 | |
    4321 | 7 | 2 |
    1234 | | 1 |

    I expected crosstab to merge the counts for userid 1234 into one !

    Any help would be greatly apreciated,
    Thanks,
    Markus

Comments are closed.