Re: Group By? - Mailing list pgsql-general

Guy



Sorry about the chart. It held together when I sent it to myself.



I'll try to make it clear in the way you suggest, by a truncated example.



Create table control (device_id serial, type varchar, association int4)

Create table auto_control (loop_id serial, monitor int4, valve int4)



Insert into control (type, association) Note - mon and valve are types of
device that together make a loop. A loop can be from 1 to 7 devices..

Values ('mon', '1') - serial 1

Values ('valve', '2') - serial 2

Values ('mon', '2') - serial 3

Values ('valve', '1') - serial 4

Values ('valve', '2') - serial 5



I want to transfer the serial device_id number for mon '1' into the same row
as valve '1' in the tables auto_control. Similarily



Those two rows would look like this.

Table (loop_id serial, monitor int4, valve int4)

Row 1 ( 1, 1, 4, )

Row 2 (2, 2, 3, 5)



Once this is done the devices will be organized into loops and each device
in the loop will have a direct link to other parts of the database.



I would like to know if this is possible with SQL, or would it be more
suited to the host language?



Would it be possible in SQL to have the information transferred into the
auto_control table as the information is being entered or would the control
table need to be fully completed?



Hope this is finally clear.



Thanks for you help.



Bob

----- Original Message -----
From: "Guy Rouillier" <guyr@masergy.com>
To: "PostgreSQL General" <pgsql-general@postgresql.org>
Sent: Wednesday, November 23, 2005 2:17 PM
Subject: Re: [GENERAL] 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@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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org