Dynamic column names in a postgres crosstab query

Question

I am trying to pivot the data in a query in postgres. The query I am currently using is as follows

SELECT
  product_number,
  month,
  sum(quantity)
FROM forecasts
WHERE date_trunc('month', extract_date) = date_trunc('month', current_date)
GROUP BY product_number, month
ORDER BY product_number, month;

The output of the query is something like what is shown below where each product will have 13 months of data.

+--------+------------+----------+
| Number |   Month    | Quantity |
+--------+------------+----------+
|      1 | 2016-10-01 |     7592 |
|      1 | 2016-11-01 |     6796 |
|      1 | 2016-12-01 |     6512 |
|      1 | 2017-01-01 |     6160 |
|      1 | 2017-02-01 |     6475 |
|      1 | 2017-03-01 |     6016 |
|      1 | 2017-04-01 |     6616 |
|      1 | 2017-05-01 |     6536 |
|      1 | 2017-06-01 |     6256 |
|      1 | 2017-07-01 |     6300 |
|      1 | 2017-08-01 |     5980 |
|      1 | 2017-09-01 |     5872 |
|      1 | 2017-10-01 |     5824 |
+--------+------------+----------+

I am trying to pivot the data so that it looks something like

+--------+-----------+-----------+-----------+----------+-----+
| Number | 2016-10-1 | 2016-11-1 | 2016-12-1 | 2017-1-1 | ... |
+--------+-----------+-----------+-----------+----------+-----+
| 1      |       100 |       100 |       200 |      250 | ... |
| ...    |           |           |           |          |     |
+--------+-----------+-----------+-----------+----------+-----+

Where all the data for each product is shown in a row for the 13 months.

I tried using a basic crosstab query

SELECT *
FROM
crosstab('SELECT product_number, month::TEXT, sum(quantity)
   FROM forecasts
   WHERE date_trunc(''month'', extract_date) = date_trunc(''month'', ''2016-10-1''::DATE)
   GROUP BY product_number, month
   ORDER BY product_number, month')
    As mthreport(product_number text, m0 DATE, m1 DATE, m2 DATE,
        m3 DATE, m4 DATE, m5 DATE, m6 DATE,
        m7 DATE, m8 DATE, m9 DATE, m10 DATE,
        m11 DATE, m12 DATE, m13 DATE)

But I get the following error

ERROR: invalid return type Detail: SQL rowid datatype does not match return rowid datatype.

If the column name were set in the crosstab i.e. if I could define and put the names into the crosstab output this works, but since the dates keep changing I am not sure how to define them

I think I missing something very basic here. Any help would be really appreciated.

m1 int, m2 int.... not date

Roman Tkachuk
– Roman Tkachuk

2017-02-11 06:08:59 +00:00
Commented Feb 11, 2017 at 6:08 — Roman Tkachuk
– Roman Tkachuk, Commented Feb 11, 2017 at 6:08

Tajinder · Accepted Answer · 2017-02-11 09:27:45Z

1

Hoping, i have understood your problem correctly.

Column m1, m2 .. m13 are not of date type. These columns will contain sum of quantity. So, data type will be same as sum(quantity).

I think below query will solve your problem

SELECT *
FROM
 crosstab($$SELECT product_number, month, sum(quantity)::bigint
   FROM forecasts
   GROUP BY product_number, month
   ORDER BY product_number, month$$)
    As mthreport(product_number int, m0 bigint, m1 bigint, m2 bigint,
        m3 bigint, m4 bigint, m5 bigint, m6 bigint,
        m7 bigint, m8 bigint, m9 bigint, m10 bigint,
        m11 bigint, m12 bigint , m13 bigint)

answered Feb 11, 2017 at 9:27

Tajinder

2,3484 gold badges38 silver badges58 bronze badges

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

1 Comment

Pras Over a year ago

Thanks for that. I seem to have misunderstood the documentation. Changing the type to numeric fixed it.

Collectives™ on Stack Overflow

Dynamic column names in a postgres crosstab query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related