Expanding the crosstab function to extra rows - Mailing list pgsql-general

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)
ASview_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