PostgreSQL crosstab function

Question

I have some problem with crosstab function.

My table "t" is

date;name;hour;cause;c_p
"2013-06-12";167;14;0;2
"2013-06-12";167;16;0;3
"2013-06-12";167;16;0;4
"2013-06-12";167;19;1;1
"2013-06-12";167;19;0;4

I'd have this "pivot table" t_pivot

day;name;hour;cause_0;cause_1
"2013-06-12";167;14;2;0 -----sum(c_p)
"2013-06-12";167;16;7;0
"2013-06-12";167;19;4;1

Sql Code is

SELECT  * from crosstab (
    'SELECT  day,name,hour,cause, SUM(c_p) AS c_p
        FROM t
        GROUP BY 1,2,3,4
        ORDER BY 3 ',

     'SELECT DISTINCT cause 
         FROM i
         ORDER BY 1')

AS t_pivot (day date, name integer,hour integer, cause_0 integer,cause_1 integer)

Query result is one row table depending by the "ORDER BY"

ORDER BY 3
"2013-06-12";167;14;4;1

ORDER BY 1, ORDER BY 2
"2013-06-12";167;14;7;1

Where is the error? Thanks f.

roman · Accepted Answer · 2013-09-12 14:16:52Z

1

I haven't used crosstab function and can't test it now (there's no tablefunc extension on sqlfiddle), but in general I'd prefer simple SQL if I need such a pivot:

select
    date,
    hour,
    sum(case when cause = 0 then c_p else 0 end) cause_0,
    sum(case when cause = 1 then c_p else 0 end) cause_1
from t
group by date, hour
order by hour

sql fiddle demo

I think it's easier to maintain and read it in the future (but this is subjective opinion).

update This one works (hour used as a row_name, date and name are extra columns):

SELECT  * from crosstab (
    'select hour, date, name, cause, sum(c_p) as c_p
     from t
     group by 1, 2, 3, 4
     order by 1',
    'select distinct cause from t order by 1')
AS t_pivot (hour integer, date timestamp, name integer, cause_0 integer,cause_1 integer)

From documentation:

source_sql is a SQL statement that produces the source set of data. This statement must return one row_name column, one category column, and one value column. It may also have one or more "extra" columns. The row_name column must be first. The category and value columns must be the last two columns, in that order. Any columns between row_name and category are treated as "extra". The "extra" columns are expected to be the same for all rows with the same row_name value.

also

In practice the source_sql query should always specify ORDER BY 1 to ensure that values with the same row_name are brought together. However, ordering of the categories within a group is not important. Also, it is essential to be sure that the order of the category_sql query's output matches the specified output column order.

edited Sep 12, 2013 at 14:16

answered Sep 12, 2013 at 13:24

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

franco_b Over a year ago

Yes But I have to use crosstab because this is a semplifyng example of a more complex table

roman Over a year ago

@user2418290 see updated, still have to read docs to understand why it's working like this

MatheusOl Over a year ago

I think that is not quite correct with what the OP expect, because if you insert same hour on different days it will generate an inconsistent result.

MatheusOl · Accepted Answer · 2013-09-12 14:53:58Z

Seems that crosstab function expect only one column to hold the row identification, and, if I got that right, you have 3 columns: day, name and hour. But crosstab also allow extra columns, that it doesn't treat it as special, it just add it to the result.

So, for your case you must get these three columns and represent them as only one. I'm not sure if it is the best approach, but I used the row constructor to do that (so we don't need to care about data types):

SELECT  * from crosstab (                                           
    'select row(day,hour,name),day, hour, name, cause, sum(c_p) as c_p
     from t
     group by 2, 3, 4, 5
     order by 1',
    'VALUES(0),(1)')
AS t_pivot (row_name text, day date, hour int, name integer, cause_0 integer,cause_1 integer);

This will give a result like this:

      row_name       |    day     | hour | name | cause_0 | cause_1 
---------------------+------------+------+------+---------+---------
 (2013-06-12,14,167) | 2013-06-12 |   14 |  167 |       2 |      --
 (2013-06-12,16,167) | 2013-06-12 |   16 |  167 |       7 |      --
 (2013-06-12,19,167) | 2013-06-12 |   19 |  167 |       4 |       1
 (2013-06-13,14,167) | 2013-06-13 |   14 |  167 |      10 |      --
(4 rows)

You don't need to worry with the first column, it is actually useless for you, so we can just remove it:

SELECT day,hour,name,cause_0,cause_1 FROM (SELECT  * from crosstab (
    'select row(day,hour,name),day, hour, name, cause, sum(c_p) as c_p
     from t
     group by 2, 3, 4, 5
     order by 1',
    'VALUES(0),(1)')
AS t_pivot (row_name text, day date, hour int, name integer, cause_0 integer,cause_1 integer)) AS t;

One more thing. Notice that I used VALUES for the second argument instead of SELECT DISTINCT, it is a better approach if you are sure these will be the only available values, if it is not static, then the AS t_pivot ... should be dynamic also.

+1 read your comment from mobile and thought about using row as first column but you already did that. I still prefer manual pivot with group by :)

Collectives™ on Stack Overflow

PostgreSQL crosstab function

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related