Re: Crosstab Confusion - Mailing list pgsql-sql

I'm flying blind here since I have nothing that looks like the structure you described to experiment on, but based on
somecrosstabs I have set up this should get you started. Explanatory notes follow.<br /><br />SELECT pivot.*<br /> FROM
crosstab('<br/>    --row header, column header, cell value<br />    SELECT customer_id, extract(day FROM date),
sum(amount)<br/>    WHERE extract(month FROM date) = 1 --desired month, 1=Jan, 2=Feb, etc.<br />    FROM your_table<br
/>    GROUP BY 1, 2<br />    ORDER BY 1, 2<br />    ','<br />    --list of column headers<br />    SELECT 1, 2, 3, […]
31<br/>    ') <br />    --list of column names for final result set<br />    --does not have to match column names from
crosstab()section!<br />     pivot(customer_id integer, day1 numeric(10,4), day2 numeric(10,4), day3 numeric(10,4), […]
day31numeric(10,4))<br />;<br /><br />You basically have three parts:<br /><br />1) SELECT query in the form (row
header,column header, cell value). In this case it is an aggregate query so that  you can sum the transactions over a
givenday.<br /><br />2) List of column headers. If you want, this can SELECT from another table, so you can have a
tablewith rows 1, 2, 3, etc and use it to select the days from the month instead of listing them manually.<br /><br
/>3)List of output columns, which follows "pivot" in the text above. Note that "pivot" is an arbitrary name. You can
usefoo, bar, or whatever, but that will be the name of the table which must be used to reference the columns in the top
SELECTlist.<br /><br />Note that my somewhat bizarre indenting / end of line structure is designed so that, when using
pgAdmin,I can very quickly select and execute just the value query or column header query, which lets me experiment as
Ibuild the crosstab.<br /><br />Note, finally, that the list of output columns (following "pivot") should be one more
thanthe number of columns in section 2 (technically, you can circumvent this limitation, but it's complicated and seems
unnecessaryfor your use case). That could be problematic when running this query in the middle of the month and the
numberof value rows per customer doesn't match the number expected in the list of column headers and list of output
columns.I don't know of any way to make the list of output columns vary dynamically, but perhaps someone else here
does?Otherwise, you would have to either (a) make 31 different crosstab queries and choose which one to run based on
theday of the month, or (b) come up with some way to "pad" the value table created in part 1 so that it lists
customer_id,day, 0 for all days which have no customer transactions. (Perhaps create a dummy customer_id that has no
transactionsfor all days in the month, which should be enough to trick the crosstab function into thinking it has
somethingto fill the last columns.<br /><br />--Lee<br /><div class="gmail_quote"><br /></div>-- <br />Lee
Hachadoorian<br/>PhD Student, Geography<br />Program in Earth & Environmental Sciences<br />CUNY Graduate Center<br
/>