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

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
""BillR"" <iambill(at)williamrosmus(dot)com> wrote in message news:004a01caaa01$7b92ade0$72b809a0$(at)com(dot)(dot)(dot)
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(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Davor J.
Sent: February-09-10 2:02 PM
To: pgsql-general(at)postgresql(dot)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(dot)romano(at)notorand(dot)it> wrote in message news:3eff28921002081133h4b0d7fabm96cc1bc08e5794dc(at)mail(dot)gmail(dot)com(dot)(dot)(dot)

Look for crosstab in the documentation.

Il giorno 8 feb, 2010 8:21 p., "Davor J." <DavorJ(at)live(dot)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(at)postgresql(dot)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