Message-ID: <3DF76E6F.144FEA5A@nsd.ca>
Date: Wed, 11 Dec 2002 11:57:19 -0500
From: Jean-Luc Lachance <jllachan@nsd.ca>
MIME-Version: 1.0
To: Frank Bax <fbax@sympatico.ca>
Cc: javier garcia <andresjavier.garcia@wanadoo.es>,
	"pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: union query doubt:
References: <5.1.1.6.0.20021211113406.00a08510@pop6.sympatico.ca>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

I think you meant:

SELECT date,
sum( case when point = 1 then flow else 0 end) as flow1,
sum( case when point = 2 then flow else 0 end) as flow2,
sum( case when point = 3 then flow else 0 end) as flow3,
sum( case when point = 4 then flow else 0 end) as flow4,
sum( case when point = 5 then flow else 0 end) as flow5,
sum( case when point = 6 then flow else 0 end) as flow6
from samples group by date;


Frank Bax wrote:
> 
> At 11:21 AM 12/11/02, javier garcia wrote:
> >I've got a table with three fields: DATE, POINT, FLOW. The POINT field can
> >have values among 1 and 6. So, for a same date I have six different points
> >with the correspondings flows.
> >I would like to make a query to obtain something like:
> >DATE POINT1 POINT2 POINT3 POINT4 POINT5 POINT6
> >
> >where for a date I have the flows data of the different points.
> 
> SELECT date,
> case when point = 1 then flow else 0 end as flow1,
> case when point = 2 then flow else 0 end as flow2,
> case when point = 3 then flow else 0 end as flow3,
> case when point = 4 then flow else 0 end as flow4,
> case when point = 5 then flow else 0 end as flow5,
> case when point = 6 then flow else 0 end as flow6
> from samples
> 
> There have been several messages recently about this - search on crosstab
> or pivot - a couple of other options were presented.
> 
> Frank
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org