From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: One column to multiple columns based on constraints? |
Date: | 2010-02-10 08:47:25 |
Message-ID: | 4B72729D.7020302@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Davor J. wrote:
> Let's say you have a table:
> CREATE TABLE t (
> time date,
> data integer
> )
>
> Suppose you want a new table that has columns similar to the following:
> "(x.time, x.data, y.time, y.data, z.time, z.data)" where x.time, y.time and
> z.time columns are constrained (for example x.time >2007 AND x.time <2008,
> y.time >2008 AND y.time < 2009, z.time > 2010)
>
> How would you do this. Note that you can not use JOIN as there is no
> relationship.
>
> Currently I came up with something like this:
>
> SELECT X.*, (SELECT Y.time, Y.data FROM t AS Y WHERE Y.time = X.time + 1),
> (SELECT Z.time .) FROM t AS X WHERE X.time >2007 AND X.time <2008
>
Um, why can't you use a join?
SELECT X.*, Y.time, Y.data FROM t AS X JOIN t as Y ON (Y.time = X.time + '1 year'::INTERVAL)
WHERE X.time >= '2007-01-01'::DATE AND X.time < '2008-01-01'::DATE;
I believe should be functionally equivalent to your nested select. I'm
not real sure what you're trying to imply with your date > integer
comparisions, so I tried to be a little more rigorous there.
From | Date | Subject | |
---|---|---|---|
Next Message | Allan Kamau | 2010-02-10 09:33:18 | Re: xpath |
Previous Message | Otandeka Simon Peter | 2010-02-10 08:34:01 | Re: xpath |