Re: Group By?

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