Re: One column to multiple columns based on constraints?

Thank you for the input John.

You understood my sketch just fine and your JOIN is indeed equivalent to the
nested select. I said there is no relationship, but in my nested select I
implicitly created a relationship. I should have been more explicit here:
what I meant is that there "should" be no relationship.

From what I know of SQL, one always needs a relationship to append some row
to the one from FROM clause. I want to append them without a relationship.
So if my base table "t" has columns (time and data), I want a new table
which has columns (time2008, data2008, time2009, data2009, time2010,
data2010,...) where rows of time2009 and data2009 are constrained by 'year
2008' , but are in no relationship with the rows of time2008. (NULL should
be used if there are more in year2008 column, than in year2009 column, vice
versa.)

Regards,
Davor

"John R Pierce" <pierce(at)hogranch(dot)com> wrote in message
news:4B72729D(dot)7020302(at)hogranch(dot)com(dot)(dot)(dot)
> 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.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>