Re: Group By? - Mailing list pgsql-general

Bob Pawley wrote:
> 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.

Sorry, Bob, I think the reason you haven't gotten a response is that the
information you are trying to convey below is very unclear.  Perhaps you
can just provide a create table statement and a bunch of insert
statements?  Then summarize again what you are trying to accomplish.

>
> 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



--
Guy Rouillier