Re: Group By?

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(at)wolff(dot)to>
> To: "Bob Pawley" <rjpawley(at)shaw(dot)ca>
> Cc: "Guy Rouillier" <guyr(at)masergy(dot)com>; "Postgre General"
> <pgsql-general(at)postgresql(dot)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(at)shaw(dot)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(at)masergy(dot)com>
>>> To: "Postgre General" <pgsql-general(at)postgresql(dot)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