Обсуждение: One column to multiple columns based on constraints?

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

But it's somewhat awkward. I thought maybe someone has better idea's. Any
input is welcome.

Look for crosstab in the documentation.

Il giorno 8 feb, 2010 8:21 p., "Davor J." <DavorJ@live.com> ha scritto:

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

But it's somewhat awkward. I thought maybe someone has better idea's. Any
input is welcome.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"Vincenzo Romano" <vincenzo.romano@notorand.it> wrote in message news:3eff28921002081133h4b0d7fabm96cc1bc08e5794dc@mail.gmail.com...

Look for crosstab in the documentation.

Il giorno 8 feb, 2010 8:21 p., "Davor J." <DavorJ@live.com> ha scritto:

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

But it's somewhat awkward. I thought maybe someone has better idea's. Any
input is welcome.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

""BillR"" <iambill@williamrosmus.com> wrote in message news:004a01caaa01$7b92ade0$72b809a0$@com...

Is there any reason it has to be done in one DML statement? Can you write a procedure to this in multiple steps?

 

BillR

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Davor J.
Sent: February-09-10 2:02 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] R: One column to multiple columns based on constraints?

 

Crosstab is indeed very interesting. Thank you for the suggestion Vincenzo.

 

regards

Davor

"Vincenzo Romano" <vincenzo.romano@notorand.it> wrote in message news:3eff28921002081133h4b0d7fabm96cc1bc08e5794dc@mail.gmail.com...

Look for crosstab in the documentation.

Il giorno 8 feb, 2010 8:21 p., "Davor J." <DavorJ@live.com> ha scritto:

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

But it's somewhat awkward. I thought maybe someone has better idea's. Any
input is welcome.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--------------------------------

Spam/Virus scanning by CanIt Pro

For more information see http://www.kgbinternet.com/SpamFilter.htm

To control your spam filter, log in at http://filter.kgbinternet.com

---

Spam
Not spam
Forget previous vote

__________ Information from ESET Smart Security, version of virus signature database 4852 (20100209) __________

The message was checked by ESET Smart Security.

http://www.eset.com

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.

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@hogranch.com> wrote in message
news:4B72729D.7020302@hogranch.com...
> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>