Thread: One column to multiple columns based on constraints?

One column to multiple columns based on constraints?

From
"Davor J."
Date:
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.



R: One column to multiple columns based on constraints?

From
Vincenzo Romano
Date:

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

Re: R: One column to multiple columns based on constraints?

From
"Davor J."
Date:
Crosstab is indeed very interesting. Thank you for the suggestion Vincenzo.
 
regards
Davor

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

Re: R: One column to multiple columns based on constraints?

From
"BillR"
Date:

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

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



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

The message was checked by ESET Smart Security.

http://www.eset.com

Re: R: One column to multiple columns based on constraints?

From
"Davor J."
Date:
I have limited access to the database. I can not write stored procedures, so it has to be pure SQL.
 
But yes, PL opens other possibilities.
 
Davor

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

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



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

The message was checked by ESET Smart Security.

http://www.eset.com

Re: One column to multiple columns based on constraints?

From
John R Pierce
Date:
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.



Re: One column to multiple columns based on constraints?

From
"Davor J."
Date:
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
>