I am trying to use the crosstab function of the contrib tablefunc.
Reading the README, I believe I am supposed to be using crosstab(sql, N)
for my situation and wondering if the SQL can be based on a view? I have
this view created that gives me each sales rep and their total number of
units sold and total revenue for each month:
CREATE VIEW "public"."view_pick1" (
rep,
month,
units,
revenue)
AS
SELECT users.user_login AS rep, date_part('month'::text,
current_clients.start_date) AS "month", count(companies.company_id) AS
units, sum(companies.company_revenue) AS revenue
FROM ((companies JOIN current_clients ON ((companies.company_id =
current_clients.client_id))) JOIN users ON ((companies.company_sales_rep =
users.user_id)))
GROUP BY users.user_login, date_part('month'::text, current_clients.start_date)
ORDER BY users.user_login, date_part('month'::text, current_clients.start_date);
Trying to make a crosstab, let's say just for units, this is what I'm
attempting, which is wrong of course, can someone enlighten me as this
is my first crosstab.
select * from crosstab('select rep, month, units from view_pick1 order by 1,2;', 12) AS view_pick1(rep varchar, jan
doubleprecision, feb double precision, mar double precision, apr double precision, may double precision, jun double
precision,jul double precision, aug double precision, sep double precision, oct double precision, nov double precision,
decdouble precision);
Error is: ERROR: return and sql tuple descriptions are incompatible
SQL state: 42601
Not sure what that means, I tried to match up the view field types with
the returned fields. My sql produces the following after which is what I
would like to get. Am I even going about this correctly?
rep | month | units
--------------+-------+-------
aespinal | 5 | 4
aespinal | 6 | 3
asmith | 1 | 1
athranow | 1 | 5
athranow | 2 | 1
athranow | 3 | 2
athranow | 4 | 1
rep jan feb mar apr may jun etc...
---------+-----+-----+-----+-----+-----+-----+-
aespinal 4 3
asmith 1
athranow 5 1 2 1
Thanks for the help!
--
Robert
