DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=OuCJNegA1JkR6Z0DH8ct100tyylVB8ownMncF+dw1VvYY2nJfSDE7XmfvUsDKDICyH
	4ucVQUrYJWKMqIaZGD5ndIrDt2SKBQV/EtfxCsziwE+ZeI7bQuXCRTmdkrHYJ1eMyDgY
	35ZezxQF/LeZmNt/O+QXNb1NX7/bGl40JAxLk=
MIME-Version: 1.0
In-Reply-To: <C5C50165-6162-460C-8E13-A0A76D0B726B@sherman.ca>
References: <C5C50165-6162-460C-8E13-A0A76D0B726B@sherman.ca>
Date: Mon, 1 Feb 2010 11:34:44 -0500
Message-ID: <5ab13581002010834k11c1be7ci4b4aef1b6778be4f@mail.gmail.com>
Subject: Re: Crosstab Confusion
From: Lee Hachadoorian <lee.hachadoorian@gmail.com>
To: Adam Sherman <adam@sherman.ca>
Cc: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=001636283dce1a98af047e8c908e

--001636283dce1a98af047e8c908e
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

I'm flying blind here since I have nothing that looks like the structure yo=
u
described to experiment on, but based on some crosstabs I have set up this
should get you started. Explanatory notes follow.

SELECT pivot.*
FROM crosstab('
    --row header, column header, cell value
    SELECT customer_id, extract(day FROM date), sum(amount)
    WHERE extract(month FROM date) =3D 1 --desired month, 1=3DJan, 2=3DFeb,=
 etc.
    FROM your_table
    GROUP BY 1, 2
    ORDER BY 1, 2
    ','
    --list of column headers
    SELECT 1, 2, 3, [=85] 31
    ')
    --list of column names for final result set
    --does not have to match column names from crosstab() section!
    pivot(customer_id integer, day1 numeric(10,4), day2 numeric(10,4), day3
numeric(10,4), [=85] day31 numeric(10,4))
;

You basically have three parts:

1) SELECT query in the form (row header, column header, cell value). In thi=
s
case it is an aggregate query so that  you can sum the transactions over a
given day.

2) List of column headers. If you want, this can SELECT from another table,
so you can have a table with rows 1, 2, 3, etc and use it to select the day=
s
from the month instead of listing them manually.

3) List of output columns, which follows "pivot" in the text above. Note
that "pivot" is an arbitrary name. You can use foo, bar, or whatever, but
that will be the name of the table which must be used to reference the
columns in the top SELECT list.

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 I build the
crosstab.

Note, finally, that the list of output columns (following "pivot") should b=
e
one more than the number of columns in section 2 (technically, you can
circumvent this limitation, but it's complicated and seems unnecessary for
your use case). That could be problematic when running this query in the
middle of the month and the number of 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 ru=
n
based on the day 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 transactions for all days in the month, which shoul=
d
be enough to trick the crosstab function into thinking it has something to
fill the last columns.

--Lee

--=20
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center

--001636283dce1a98af047e8c908e
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

I&#39;m flying blind here since I have nothing that looks like the structur=
e you described to experiment on, but based on some crosstabs I have set up=
 this should get you started. Explanatory notes follow.<br><br>SELECT pivot=
.*<br>
FROM crosstab(&#39;<br>=A0=A0=A0 --row header, column header, cell value<br=
>=A0=A0=A0 SELECT customer_id, extract(day FROM date), sum(amount)<br>=A0=
=A0=A0 WHERE extract(month FROM date) =3D 1 --desired month, 1=3DJan, 2=3DF=
eb, etc.<br>=A0=A0=A0 FROM your_table<br>
=A0=A0=A0 GROUP BY 1, 2<br>=A0=A0=A0 ORDER BY 1, 2<br>=A0=A0=A0 &#39;,&#39;=
<br>=A0=A0=A0 --list of column headers<br>=A0=A0=A0 SELECT 1, 2, 3, [=85] 3=
1<br>=A0=A0=A0 &#39;) <br>=A0=A0=A0 --list of column names for final result=
 set<br>=A0=A0=A0 --does not have to match column names from crosstab() sec=
tion!<br>
=A0=A0=A0 pivot(customer_id integer, day1 numeric(10,4), day2 numeric(10,4)=
, day3 numeric(10,4), [=85] day31 numeric(10,4))<br>;<br><br>You basically =
have three parts:<br><br>1) SELECT query in the form (row header, column he=
ader, cell value). In this case it is an aggregate query so that=A0 you can=
 sum the transactions over a given day.<br>
<br>2) List of column headers. If you want, this can SELECT from another ta=
ble, so you can have a table with rows 1, 2, 3, etc and use it to select th=
e days from the month instead of listing them manually.<br><br>3) List of o=
utput columns, which follows &quot;pivot&quot; in the text above. Note that=
 &quot;pivot&quot; is an arbitrary name. You can use foo, bar, or whatever,=
 but that will be the name of the table which must be used to reference the=
 columns in the top SELECT list.<br>
<br>Note that my somewhat bizarre indenting / end of line structure is desi=
gned so that, when using pgAdmin, I can very quickly select and execute jus=
t the value query or column header query, which lets me experiment as I bui=
ld the crosstab.<br>
<br>Note, finally, that the list of output columns (following &quot;pivot&q=
uot;) should be one more than the number of columns in section 2 (technical=
ly, you can circumvent this limitation, but it&#39;s complicated and seems =
unnecessary for your use case). That could be problematic when running this=
 query in the middle of the month and the number of value rows per customer=
 doesn&#39;t match the number expected in the list of column headers and li=
st of output columns. I don&#39;t know of any way to make the list of outpu=
t 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 the day of the month, or (b) come up with some wa=
y to &quot;pad&quot; the value table created in part 1 so that it lists cus=
tomer_id, day, 0 for all days which have no customer transactions. (Perhaps=
 create a dummy customer_id that has no transactions for all days in the mo=
nth, which should be enough to trick the crosstab function into thinking it=
 has something to fill the last columns.<br>
<br>--Lee<br><div class=3D"gmail_quote"><br></div>-- <br>Lee Hachadoorian<b=
r>PhD Student, Geography<br>Program in Earth &amp; Environmental Sciences<b=
r>CUNY Graduate Center<br>

--001636283dce1a98af047e8c908e--