Finally figured out what was wrong with my crosstab that I posted for
help yesterday. I was really close, just need to set the right types. I
have it working using the crosstab(text sql, int N) function. This
displays a crosstab from my view below for units sold by each sales rep
under each month...
SELECT view_pick1_months.rep, view_pick1_months."month", view_pick1_data.units,
view_pick1_data.revenue
FROM (view_pick1_months LEFT JOIN view_pick1_data ON
((((view_pick1_months.rep)::text = (view_pick1_data.rep)::text) AND
(view_pick1_months."month" = view_pick1_data.nmonth))))
ORDER BY view_pick1_months.rep, view_pick1_months."month";
primepay=# select * from view_pick1 where rep ='aespinal';
rep | month | units | revenue
----------+-------+-------+---------
aespinal | 1 | 10 | 500
aespinal | 2 | 9 | 100
aespinal | 3 | 8 | 250
aespinal | 4 | 7 | 1000
aespinal | 5 | 6 | 500
aespinal | 6 | 5 | 250
aespinal | 7 | 4 | 300
aespinal | 8 | 3 | 150
aespinal | 9 | 2 | 100
aespinal | 10 | 1 | 250
aespinal | 11 | 2 | 5000
aespinal | 12 | 3 | 2500
In my crosstab, I only use units right now and it works fine...
primepay=# select * from crosstab('select rep, month, units from view_pick1 where rep =''aespinal'' order by 1,2;', 12) AS view_pick1(rep varchar, jan bigint, feb bigint, mar bigint, apr bigint, may bigint, jun bigint, jul bigint, aug bigint, sep bigint, oct bigint, nov bigint, dec bigint);
rep | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec
----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----
aespinal | 10 | 9 | 8 | 7 | 6 | 5 | 4 | 3 | 2 | 1 | 2 | 3
Now, what I'd like to do is use the synopsis crosstab(text source_sql,
text category_sql) and include revenue on another line with units and
revenue being the extra_col in the README example for that function. So,
according to the readme, I need to produce the following data, but I am
perplexed at how to do this, can anyone help me produce the following
data:
rep | month | extra | amount
----------+-------+---------+---------
aespinal | 1 | units | 10
aespinal | 1 | revenue | 500
aespinal | 2 | units | 9
aespinal | 2 | revenue | 100
aespinal | 3 | units | 8
aespinal | 3 | revenue | 250
aespinal | 4 | units | 7
aespinal | 4 | revenue | 1000
aespinal | 5 | units | 6
aespinal | 5 | revenue | 500
aespinal | 6 | units | 5
aespinal | 6 | revenue | 250
aespinal | 7 | units | 4
aespinal | 7 | revenue | 300
aespinal | 8 | units | 3
aespinal | 8 | revenue | 150
aespinal | 9 | units | 2
aespinal | 9 | revenue | 100
aespinal | 10 | units | 1
aespinal | 10 | revenue | 250
aespinal | 11 | units | 2
aespinal | 11 | revenue | 5000
aespinal | 12 | units | 3
aespinal | 12 | revenue | 2500
If I can accomplish the above, then I think my new crosstab would output
like this:
rep | extra | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec
----------+---------+-----+-----+-----+-----+------+-----+-----+-----+-----+-----+------+-----
aespinal | units | 10 | 9 | 8 | 7 | 6 | 5 | 4 | 3 | 2 | 1 | 2 | 3
aespinal | revenue | 500 | 100 | 250 | 1000 | 500 | 250 | 300 | 150 | 100 | 250 | 5000 | 2500
--
Robert
