From Theo.Galanakis@lonelyplanet.com.au Wed Mar  2 04:33:46 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 337CB55FF1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Mar 2005 04:33:46 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 11222-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Mar 2005 04:33:44 +0000 (GMT)
Received: from angel.lonelyplanet.com.au (sweep.lonelyplanet.com.au
	[202.147.44.192])
	by svr1.postgresql.org (Postfix) with ESMTP id B2E8C55BBC
	for <pgsql-sql@postgresql.org>; Wed,  2 Mar 2005 04:33:42 +0000 (GMT)
Received: from Ganesh.au.lpint.net ([192.168.61.44]) by sweep with InterScan
	Messaging Security Suite; Wed, 02 Mar 2005 15:34:23 +1100
Received: by ganesh.au.lpint.net with Internet Mail Service (5.5.2657.72)
	id <FY6HF4BA>; Wed, 2 Mar 2005 15:30:28 +1100
Message-ID: <D1444817B78AB546BF2896C2B70E7F04371F22@ganesh.au.lpint.net>
From: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
To: pgsql-sql@postgresql.org
Subject: crosstab
Date: Wed, 2 Mar 2005 15:30:27 +1100 
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2657.72)
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C51EE0.8F77D530"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.363 tagged_above=0 required=5 tests=AWL, HTML_60_70,
	HTML_MESSAGE, HTML_TEXT_AFTER_BODY, HTML_TEXT_AFTER_HTML
X-Spam-Level: 
X-Archive-Number: 200503/5
X-Sequence-Number: 20484

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C51EE0.8F77D530
Content-Type: text/plain


Hi I have looked into the tablefunc / crosstab contrib for postgres and it
appears like it can't perform what I need.

The crosstab function converts this : 

		 row_name    cat    value
            ----------+-------+-------
              row1      cat1    val1
              row1      cat2    val2
              row1      cat3    val3
              row1      cat4    val4
              row2      cat1    val5
              row2      cat2    val6
              row2      cat3    val7
              row2      cat4    val8

To this : 

		row_name   category_1   category_2
           ---------+------------+------------
             row1        val1         val2
             row2        val5         val6

Is it possible to do the opposite and go from a column(denormalized)
structure to a row(normalized) structure.

I have a table that is similarly stuctured like so:

Melbourne_figures		Sydney_figures	Adelaide_figures   etc...
	10			20			22
	10			22			29
	...


However I wish to convert like so :

Melbourne	20
Sydney	42
Adelaide	51

I have tried using unions or subselects however the table is quite large and
it takes far too long to run. The most efficient way would be to create a
stored proc that uses a cursor to loop through the table transforming the
data into the new table structure. However I would appreciate your feeback
before writing this procedure?

Theo


______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright.  If you
have received this email in error, please advise the sender and delete
it.  If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone.  You must not copy or 
communicate to others content that is confidential or subject to 
copyright, unless you have the consent of the content owner.
------_=_NextPart_001_01C51EE0.8F77D530
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=
=3DUS-ASCII">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version=
 5.5.2654.45">
<TITLE>crosstab</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2>Hi I have looked into the tablefunc / crosstab contrib=
 for postgres and it appears like it can't perform what I need.</FONT>
</P>

<P><FONT SIZE=3D2>The crosstab function converts this : </FONT>
</P>

<P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<FONT SIZE=3D2>=
 row_name&nbsp;&nbsp;&nbsp; cat&nbsp;&nbsp;&nbsp; value</FONT>
<BR><FONT SIZE=
=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 ----------+-------+-------</FONT>
<BR><FONT SIZE=
=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; row1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cat1&nbsp;&nbsp;&nbsp;=
 val1</FONT>
<BR><FONT SIZE=
=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; row1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cat2&nbsp;&nbsp;&nbsp;=
 val2</FONT>
<BR><FONT SIZE=
=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; row1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cat3&nbsp;&nbsp;&nbsp;=
 val3</FONT>
<BR><FONT SIZE=
=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; row1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cat4&nbsp;&nbsp;&nbsp;=
 val4</FONT>
<BR><FONT SIZE=
=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; row2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cat1&nbsp;&nbsp;&nbsp;=
 val5</FONT>
<BR><FONT SIZE=
=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; row2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cat2&nbsp;&nbsp;&nbsp;=
 val6</FONT>
<BR><FONT SIZE=
=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; row2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cat3&nbsp;&nbsp;&nbsp;=
 val7</FONT>
<BR><FONT SIZE=
=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; row2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cat4&nbsp;&nbsp;&nbsp;=
 val8</FONT>
</P>

<P><FONT SIZE=3D2>To this : </FONT>
</P>

<P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=
=3D2>row_name&nbsp;&nbsp; category_1&nbsp;&nbsp; category_2</FONT>
<BR><FONT SIZE=
=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 ---------+------------+------------</FONT>
<BR><FONT SIZE=
=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p; row1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 val1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; val2</FONT>
<BR><FONT SIZE=
=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p; row2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 val5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; val6</FONT>
</P>

<P><FONT SIZE=3D2>Is it possible to do the opposite and go from a=
 column(denormalized) structure to a row(normalized) structure.</FONT>
</P>

<P><FONT SIZE=3D2>I have a table that is similarly stuctured like=
 so:</FONT>
</P>

<P><FONT SIZE=3D2>Melbourne_figures&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sydney_figures&nbsp;=
 Adelaide_figures&nbsp;&nbsp; etc...</FONT>
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=
=3D2>10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22</FONT>
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=
=3D2>10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 29</FONT>
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>...</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>However I wish to convert like so :</FONT>
</P>

<P><FONT SIZE=3D2>Melbourne&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20</FONT>
<BR><FONT SIZE=3D2>Sydney&nbsp; 42</FONT>
<BR><FONT SIZE=3D2>Adelaide&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
 51</FONT>
</P>

<P><FONT SIZE=3D2>I have tried using unions or subselects however the table=
 is quite large and it takes far too long to run. The most efficient way=
 would be to create a stored proc that uses a cursor to loop through the=
 table transforming the data into the new table structure. However I would=
 appreciate your feeback before writing this procedure?</FONT></P>

<P><FONT SIZE=3D2>Theo</FONT>
</P>

</BODY>
</HTML>
<table><tr><td bgcolor=3D#ffffff><font color=
=3D#000000>________________________________________________________________=
______<br>
This email, including attachments, is intended only for the addressee<br>
and may be confidential, privileged and subject to copyright.  If you<br>
have received this email in error, please advise the sender and delete<br>
it.  If you are not the intended recipient of this email, you must not<br>
use, copy or disclose its content to anyone.  You must not copy or <br>
communicate to others content that is confidential or subject to <br>
copyright, unless you have the consent of the content owner.<br>
</font></td></tr></table>
------_=_NextPart_001_01C51EE0.8F77D530--



From josh@agliodbs.com Wed Mar  2 04:42:59 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 8CD7B554CD
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Mar 2005 04:42:59 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 13027-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  2 Mar 2005 04:42:50 +0000 (GMT)
Received: from davinci.ethosmedia.com (server227.ethosmedia.com
	[209.128.84.227])
	by svr1.postgresql.org (Postfix) with ESMTP id 94A6E52AA6
	for <pgsql-sql@postgresql.org>; Wed,  2 Mar 2005 04:42:49 +0000 (GMT)
X-EthosMedia-Virus-Scanned: no infections found
Received: from [64.81.245.111] (account josh@agliodbs.com HELO
	temoku.sf.agliodbs.com)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8)
	with ESMTP id 7082599; Tue, 01 Mar 2005 20:44:38 -0800
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
Subject: Re: crosstab
Date: Tue, 1 Mar 2005 20:47:08 -0800
User-Agent: KMail/1.7.1
Cc: pgsql-sql@postgresql.org
References: <D1444817B78AB546BF2896C2B70E7F04371F22@ganesh.au.lpint.net>
In-Reply-To: <D1444817B78AB546BF2896C2B70E7F04371F22@ganesh.au.lpint.net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200503012047.08799.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200503/6
X-Sequence-Number: 20485

THeo,

> I have tried using unions or subselects however the table is quite large
> and it takes far too long to run. The most efficient way would be to create
> a stored proc that uses a cursor to loop through the table transforming the
> data into the new table structure. However I would appreciate your feeback
> before writing this procedure?

A proc won't run any faster ... much slower, in fact.    Unless you mean that 
you want to use it to correct the actual table structure, which is what you 
should be doing?

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



From johnf@jfcomputer.com Mon Mar  12 16:58:22 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 2538E29F0D5
	for <pgsql-sql@postgresql.org>; Mon, 12 Mar 2012 13:58:22 -0300 (ADT)
Received: from [76.14.161.106] (helo=newserver.jfcomputer.com)
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <johnf@jfcomputer.com>) id 1S78Zg-000595-VC
	for pgsql-sql@postgresql.org; Mon, 12 Mar 2012 16:58:21 +0000
Received: from localhost (unknown [127.0.0.1])
	by newserver.jfcomputer.com (Postfix) with ESMTP id CBC5AFC29
	for <pgsql-sql@postgresql.org>; Mon, 12 Mar 2012 16:58:04 +0000 (UTC)
X-Virus-Scanned: amavisd-new at site
Received: from newserver.jfcomputer.com ([127.0.0.1])
	by localhost (linux-jfp8.site [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id DPtqUc43T+wR for <pgsql-sql@postgresql.org>;
	Mon, 12 Mar 2012 09:58:03 -0700 (PDT)
Received: from linux-12.localnet (unknown [192.168.1.254])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	(Authenticated sender: johnf@jfcomputer.com)
	by newserver.jfcomputer.com (Postfix) with ESMTPSA id DC0D15931
	for <pgsql-sql@postgresql.org>; Mon, 12 Mar 2012 09:58:03 -0700 (PDT)
From: John Fabiani <johnf@jfcomputer.com>
To: "PostgreSQL (SQL)" <pgsql-sql@postgresql.org>
Subject: crosstab
Date: Mon, 12 Mar 2012 09:58:03 -0700
Message-ID: <1374902.j7hCdyTH7B@linux-12>
User-Agent: KMail/4.7.2 (Linux/3.1.9-1.4-desktop; KDE/4.7.2; x86_64; ; )
MIME-Version: 1.0
Content-Transfer-Encoding: 7Bit
Content-Type: text/plain; charset="us-ascii"
X-Host-Lookup-Failed: Reverse DNS lookup failed for 76.14.161.106 (failed)
X-Pg-Spam-Score: 1.6 (+)
X-Archive-Number: 201203/37
X-Sequence-Number: 36432

Hi,
I don't know if it because I'm as sick as dog or I'm just a plain idiot - most 
likely a little of both.

Here is my table 

week_of date, 
item_number text,
planned_demand integer.

I have 
week_of        item_number      planned
2012-02-12    00005                200
2012-02-19    00005                -30
2012-02-26    00005                -16

I want to see

item_number   2012-02-12    2012-02-19   2012-02-26
00005                   200                 -30                 -16

I actually have added fields but that should get me started.

Thanks for help from an old man with a very bad cold.

Johnf



From pavel.stehule@gmail.com Mon Mar  12 17:02:16 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id D811829F0D5
	for <pgsql-sql@postgresql.org>; Mon, 12 Mar 2012 14:02:16 -0300 (ADT)
Received: from mail-ey0-f174.google.com ([209.85.215.174])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <pavel.stehule@gmail.com>) id 1S78dS-0007Pg-5D
	for pgsql-sql@postgresql.org; Mon, 12 Mar 2012 17:02:15 +0000
Received: by eaaq12 with SMTP id q12so1262302eaa.19
	for <pgsql-sql@postgresql.org>; Mon, 12 Mar 2012 10:02:01 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type:content-transfer-encoding;
	bh=C3IE6K9A7acNLQRTvOn0+15xy61zAEqPxhH+dNABkQE=;
	b=k3uW7VIuDjcr0Ut8Z9YLC7TWTUukH5Hk33ghmalCM0wjPbQj3SvrJwZxLVYP836NY1
	eKoJJ2afkU6DHPMtjEGwO1+h3mVg/wKJESsf5Ixio/aV02LXe0WppVBswQkVxYXeuhqo
	df1SeS+Zr9eywGxKBNtkr6Z8lEc9rDf6s01mN5yNjtaPo/+x25o80mMXnjvSh9sVH2EC
	geTY3tK5JeeJN9pv1E2rCF+00mFwZOXQ5NOow35RoX1s5dVVlwaHuVL/h4IeLMvX1WQ4
	vpjAgztMlvS+YbuAxuUBhtGH5mUXe3856HkiJIsURFTMFmk6rpwKmHWghTXgs8ErzJsF
	UwHw==
Received: by 10.229.136.212 with SMTP id s20mr2644185qct.118.1331571721122;
	Mon, 12 Mar 2012 10:02:01 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.229.98.85 with HTTP; Mon, 12 Mar 2012 10:01:21 -0700 (PDT)
In-Reply-To: <1374902.j7hCdyTH7B@linux-12>
References: <1374902.j7hCdyTH7B@linux-12>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Mon, 12 Mar 2012 18:01:21 +0100
Message-ID: 
 <CAFj8pRCNAMhkkvqjrJfHmhHjGhBhza1bqeNL+ykSfqJUkxw3tw@mail.gmail.com>
Subject: Re: crosstab
To: John Fabiani <johnf@jfcomputer.com>
Cc: "PostgreSQL (SQL)" <pgsql-sql@postgresql.org>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Pg-Spam-Score: -2.7 (--)
X-Archive-Number: 201203/38
X-Sequence-Number: 36433

Hello

maybe this article helps
http://stackoverflow.com/questions/3002499/postgresql-crosstab-query

there are more ways

Regards

Pavel Stehule



2012/3/12 John Fabiani <johnf@jfcomputer.com>:
> Hi,
> I don't know if it because I'm as sick as dog or I'm just a plain idiot -=
 most
> likely a little of both.
>
> Here is my table
>
> week_of date,
> item_number text,
> planned_demand integer.
>
> I have
> week_of =C2=A0 =C2=A0 =C2=A0 =C2=A0item_number =C2=A0 =C2=A0 =C2=A0planne=
d
> 2012-02-12 =C2=A0 =C2=A000005 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0200
> 2012-02-19 =C2=A0 =C2=A000005 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0-30
> 2012-02-26 =C2=A0 =C2=A000005 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0-16
>
> I want to see
>
> item_number =C2=A0 2012-02-12 =C2=A0 =C2=A02012-02-19 =C2=A0 2012-02-26
> 00005 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 200 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -30 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -16
>
> I actually have added fields but that should get me started.
>
> Thanks for help from an old man with a very bad cold.
>
> Johnf
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql



From johnf@jfcomputer.com Mon Mar  12 18:07:32 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 721A529F0DA
	for <pgsql-sql@postgresql.org>; Mon, 12 Mar 2012 15:07:32 -0300 (ADT)
Received: from [76.14.161.106] (helo=newserver.jfcomputer.com)
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <johnf@jfcomputer.com>) id 1S79ec-0006PQ-PO
	for pgsql-sql@postgresql.org; Mon, 12 Mar 2012 18:07:31 +0000
Received: from localhost (unknown [127.0.0.1])
	by newserver.jfcomputer.com (Postfix) with ESMTP id 21F28FC29
	for <pgsql-sql@postgresql.org>; Mon, 12 Mar 2012 18:07:18 +0000 (UTC)
X-Virus-Scanned: amavisd-new at site
Received: from newserver.jfcomputer.com ([127.0.0.1])
	by localhost (linux-jfp8.site [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id 9iZ7oe7o-8mR for <pgsql-sql@postgresql.org>;
	Mon, 12 Mar 2012 11:07:13 -0700 (PDT)
Received: from linux-12.localnet (unknown [192.168.1.254])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	(Authenticated sender: johnf@jfcomputer.com)
	by newserver.jfcomputer.com (Postfix) with ESMTPSA id 016505931
	for <pgsql-sql@postgresql.org>; Mon, 12 Mar 2012 11:07:13 -0700 (PDT)
From: John Fabiani <johnf@jfcomputer.com>
To: pgsql-sql@postgresql.org
Subject: Re: crosstab
Date: Mon, 12 Mar 2012 11:07:12 -0700
Message-ID: <4791988.YSRGgLD8kK@linux-12>
User-Agent: KMail/4.7.2 (Linux/3.1.9-1.4-desktop; KDE/4.7.2; x86_64; ; )
In-Reply-To: 
 <CAFj8pRCNAMhkkvqjrJfHmhHjGhBhza1bqeNL+ykSfqJUkxw3tw@mail.gmail.com>
References: <1374902.j7hCdyTH7B@linux-12>
	<CAFj8pRCNAMhkkvqjrJfHmhHjGhBhza1bqeNL+ykSfqJUkxw3tw@mail.gmail.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 7Bit
Content-Type: text/plain; charset="us-ascii"
X-Host-Lookup-Failed: Reverse DNS lookup failed for 76.14.161.106 (failed)
X-Pg-Spam-Score: -1.1 (-)
X-Archive-Number: 201203/39
X-Sequence-Number: 36434

select *
from crosstab('select  item_number::text, week_of::date, planned_demand::text 
from holding_table order by 1,2') 
as ct(row_name text, week_of date, planned text)

The above does not work.  What am I doing wrong?
Johnf
On Monday, March 12, 2012 06:01:21 PM Pavel Stehule wrote:
> Hello
> 
> maybe this article helps
> http://stackoverflow.com/questions/3002499/postgresql-crosstab-query
> 
> there are more ways
> 
> Regards
> 
> Pavel Stehule
> 
> 2012/3/12 John Fabiani <johnf@jfcomputer.com>:
> > Hi,
> > I don't know if it because I'm as sick as dog or I'm just a plain idiot
> > - most likely a little of both.
> > 
> > Here is my table
> > 
> > week_of date,
> > item_number text,
> > planned_demand integer.
> > 
> > I have
> > week_of        item_number      planned
> > 2012-02-12    00005                200
> > 2012-02-19    00005                -30
> > 2012-02-26    00005                -16
> > 
> > I want to see
> > 
> > item_number   2012-02-12    2012-02-19   2012-02-26
> > 00005                   200                 -30                 -16
> > 
> > I actually have added fields but that should get me started.
> > 
> > Thanks for help from an old man with a very bad cold.
> > 
> > Johnf
> > 
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql



From pavel.stehule@gmail.com Mon Mar  12 18:11:54 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id C4DCA29F0DA
	for <pgsql-sql@postgresql.org>; Mon, 12 Mar 2012 15:11:54 -0300 (ADT)
Received: from mail-fa0-f46.google.com ([209.85.161.46])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <pavel.stehule@gmail.com>) id 1S79ir-0006Tg-AE
	for pgsql-sql@postgresql.org; Mon, 12 Mar 2012 18:11:53 +0000
Received: by faas1 with SMTP id s1so201701faa.19
	for <pgsql-sql@postgresql.org>; Mon, 12 Mar 2012 11:11:40 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type:content-transfer-encoding;
	bh=hkRVf0amrgAbPdIyj/l6LVuJMvlAoVqeZUYnqGYjcdY=;
	b=SL3i6JNhd6zTppCZhCy4GKUMNqRNiP8MxMn6wik/AbIPr4wbTwnzHm2KFsSSX8j22j
	YbkcNfxDefRYM9o2pbX4ugT/coV3CRMIn//djajEFlPXkCql/2rMogvZw4WAlj1BKBLV
	wHUpN2P6wKLwbnnrZVc8gIVCsdzXv0BrdD3WRiZk61tdj5/fEw5+T6QWrunS/P+jxkDm
	SmQliiqz6c4n8sTGOt+Qvcl605FHnc6de5KfRuFuT9POy4C/NSCXmnT8ZmIWzGj9mxwf
	c+QiPSapHeucS0CYK0zXgHX3cq5lOfyiziyDNkcBwgWzBloR5nWWrY0oX6llYAsRx+lk
	17Pg==
Received: by 10.224.198.133 with SMTP id eo5mr9369083qab.34.1331575900116;
	Mon, 12 Mar 2012 11:11:40 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.229.98.85 with HTTP; Mon, 12 Mar 2012 11:11:00 -0700 (PDT)
In-Reply-To: <4791988.YSRGgLD8kK@linux-12>
References: <1374902.j7hCdyTH7B@linux-12>
	<CAFj8pRCNAMhkkvqjrJfHmhHjGhBhza1bqeNL+ykSfqJUkxw3tw@mail.gmail.com>
	<4791988.YSRGgLD8kK@linux-12>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Mon, 12 Mar 2012 19:11:00 +0100
Message-ID: 
 <CAFj8pRCHWkHydkMSzXuxtjasVb1uuSxYwjwdpiSHNrBO+yHrNw@mail.gmail.com>
Subject: Re: crosstab
To: John Fabiani <johnf@jfcomputer.com>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201203/40
X-Sequence-Number: 36435

2012/3/12 John Fabiani <johnf@jfcomputer.com>:
> select *
> from crosstab('select =C2=A0item_number::text, week_of::date, planned_dem=
and::text
> from holding_table order by 1,2')
> as ct(row_name text, week_of date, planned text)
>
> The above does not work. =C2=A0What am I doing wrong?

what it does?

do you have tablefunc extension?

http://www.postgresql.org/docs/9.1/interactive/tablefunc.html

regards

Pavel Stehule

> Johnf
> On Monday, March 12, 2012 06:01:21 PM Pavel Stehule wrote:
>> Hello
>>
>> maybe this article helps
>> http://stackoverflow.com/questions/3002499/postgresql-crosstab-query
>>
>> there are more ways
>>
>> Regards
>>
>> Pavel Stehule
>>
>> 2012/3/12 John Fabiani <johnf@jfcomputer.com>:
>> > Hi,
>> > I don't know if it because I'm as sick as dog or I'm just a plain idio=
t
>> > - most likely a little of both.
>> >
>> > Here is my table
>> >
>> > week_of date,
>> > item_number text,
>> > planned_demand integer.
>> >
>> > I have
>> > week_of =C2=A0 =C2=A0 =C2=A0 =C2=A0item_number =C2=A0 =C2=A0 =C2=A0pla=
nned
>> > 2012-02-12 =C2=A0 =C2=A000005 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0200
>> > 2012-02-19 =C2=A0 =C2=A000005 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0-30
>> > 2012-02-26 =C2=A0 =C2=A000005 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0-16
>> >
>> > I want to see
>> >
>> > item_number =C2=A0 2012-02-12 =C2=A0 =C2=A02012-02-19 =C2=A0 2012-02-2=
6
>> > 00005 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 2=
00 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -30 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -16
>> >
>> > I actually have added fields but that should get me started.
>> >
>> > Thanks for help from an old man with a very bad cold.
>> >
>> > Johnf
>> >
>> > --
>> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-sql
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql