Re: Group By? - Mailing list pgsql-general

Bruno

The table I previously sent came through distorted and probabley caused
misunderstanding.

The table control and auto_control are both permanent table. I want to
reshuffle how the information is associated from one table to another with
the link between table by way of the device_id.

Following is the example which I stabilized and tested for e-mail.

Bob

      Control









      device_id
     type
     association


      serial
     varchar
     int4







      1
     mon
     1


      2
     valve
     2


      3
     valve
     1


      4
     mon
     2


      5
     valve
     1












      Auto_control









      loop_id
     mon
     valve_a
     valve_b

      serial
     int4
     int4
     int4






      1
     1
     3
     5

      2
     2
     4








----- Original Message -----
From: "Bruno Wolff III" <bruno@wolff.to>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Guy Rouillier" <guyr@masergy.com>; "Postgre General"
<pgsql-general@postgresql.org>
Sent: Monday, November 21, 2005 10:07 PM
Subject: Re: Group By?


> On Mon, Nov 21, 2005 at 21:53:10 -0800,
>  Bob Pawley <rjpawley@shaw.ca> wrote:
>> Here's what I want to do.
>>
>> Table control contains values (mon and valves) that are associated by
>> numbers inserted into the associated column.
>>
>> I want to transfer the serial _id number of the items associated by the
>> value '1' into the appropriate columns of the first row of the table
>> auto_control. All items associated with the value '2' into the second
>> row -
>> etc. etc.
>
> You don't really want to do that. Tables have fixed numbers of columns and
> what you want to do doesn't result in a fixed number of columns.
>
> If you want to generate a report with that format, then I think there is
> a contrib module (crosstabs?) that will do this kind of thing. You could
> also have a report app do it for you. In the report app method, you would
> be best to return rows ordered by association and then device_ID and have
> the
> app check for when the association value changes.
>
>>
>> Is this best accomplished by a 'group by' command or subset???
>>
>> Bob
>>      Control
>>
>>      device_ID type association
>>      serial varchar int4
>>
>>      1 mon 1
>>      2 valve 2
>>      3 valve 1
>>      4 mon 2
>>      5 valve 1
>>
>>
>>      Auto_control
>>
>>      loop_id mon valve valve
>>      serial int4 int4 int4
>>      1 1 3 5
>>      2 2 4
>>
>>
>>
>> ----- Original Message -----
>> From: "Guy Rouillier" <guyr@masergy.com>
>> To: "Postgre General" <pgsql-general@postgresql.org>
>> Sent: Monday, November 21, 2005 4:25 PM
>> Subject: Re: [GENERAL] Group By?
>>
>>
>> Converted your message to plain text as preferred on most mailing lists.
>>
>> Bob Pawley wrote:
>> >I want to take the serial ID of several values in different rows in
>> >one table and insert them into a single row of another table.
>> >
>> >Would the 'group by' command be the best way to do this?
>>
>> Could you provide an actual example?  The wording of your question is a
>> little vague and an example might help solicit an answer to the actual
>> problem.  For example, are these serial ID values all in a the same
>> column in the source table?  Or is each one in a different column?  And
>> what is the selection criteria that brings these results together?
>>
>> --
>> Guy Rouillier
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: Have you checked our extensive FAQ?
>>
>>               http://www.postgresql.org/docs/faq
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>>
>>               http://archives.postgresql.org