Postgresql Crosstab Over Two Tables

Question

Practicing with the northwind database shown below:

I'm trying to understand how to perform a crosstab while using both the orders and the order_details tables. The crosstab includes the employee_id and the ship_country from the orders table and the unit_price from the order_details table

Sample data from orders table:

sample data orders table

Sample data from the order_details table:

sample data order_details table

I thought the following would work but I couldn't get it to run:

with my_table as (
select o.employee_id, o.ship_country, od.unit_price
    from orders o
    join order_details od on o.order_id = od.order_id)

    select *
    from crosstab('select employee_id, ship_country, unit_price from my_table')
    as final_result(EmployeeID text, Austria numeric, Finland numeric, Italy numeric, France numeric, 
                    Germany numeric, Brazil numeric, Belgium numeric, Switzerland numeric);

Any thoughts and how to get this working are much appreciated. The issue seems to be that it doesn't recognize the relation to my_table. I did run the 'create extension tablefunc;' command too without issue.

Paul Maxwell · Accepted Answer · 2018-11-18T00:29:56.417

The Postgres crosstab function expects the query string as A parameter and uses the result of that in a "different context" to the query that called that function. Due to this difference of context the my_table cte simply isn't available to the internals of the function.

select *
from crosstab('select o.employee_id, o.ship_country, od.unit_price from orders o
               join order_details od on o.order_id = od.order_id')
as final_result(EmployeeID text, Austria numeric, Finland numeric, Italy numeric, France numeric, 
                Germany numeric, Brazil numeric, Belgium numeric, Switzerland numeric);

The function parameter expects an entire query, and in this instance there simply is no advantage in using a common table expression. Just move the sql of the cte into the function parameter.

For more on crosstab I suggest this answer or this

score 0 · Answer 2 · answered Nov 18 '18 at 02:32

In supplement to UBA's answer, if you want to use your cte, do your crosstab in the more standard way /using plain sql:

with my_table as (
select o.employee_id, o.ship_country, od.unit_price
from orders o
join order_details od on o.order_id = od.order_id)

select employee_id,
  Sum(case when ship_country = 'Germany' then unit_price end) as Germany,
  Sum(case when ship_country = 'Brazil' then unit_price end) as Brazil,
  ...
  FROM my_table
  GROUP BY
    employee_id;

Repeat the sum case when for each country(posting from a mobile/on mobile SO is a terrible experience - would be really hard work to type them all, sorry). Alter the aggregate function (max,sum,avg etc) to change how the data in the grid accumulates

Postgresql Crosstab Over Two Tables

2 Answers2