Yes I am trying to insert all valves into the same row as their
associated
mon.
Bob
----- Original Message -----
From: "Jim C. Nasby" <jnasby@xxxxxxxxxxxxx>
To: "Bob Pawley" <rjpawley@xxxxxxx>
Cc: "Guy Rouillier" <guyr@xxxxxxxxxxx>; "PostgreSQL General"
<pgsql-general@xxxxxxxxxxxxxx>
Sent: Monday, November 28, 2005 2:18 PM
Subject: Re: [GENERAL] Group By?
>So are you trying to get a list of all 'mon's and 'valve's for each
>given association?
>
>On Wed, Nov 23, 2005 at 04:31:04PM -0800, Bob Pawley wrote:
>>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@xxxxxxxxxxx>
>>To: "PostgreSQL General" <pgsql-general@xxxxxxxxxxxxxx>
>>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@xxxxxxxx>
>>>To: "Bob Pawley" <rjpawley@xxxxxxx>
>>>Cc: "Guy Rouillier" <guyr@xxxxxxxxxxx>; "Postgre General"
>>><pgsql-general@xxxxxxxxxxxxxx>
>>>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@xxxxxxx> 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@xxxxxxxxxxx>
>>>>>To: "Postgre General" <pgsql-general@xxxxxxxxxxxxxx>
>>>>>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
>>
>>
>>---------------------------(end of
>>broadcast)---------------------------
>>TIP 5: don't forget to increase your free space map settings
>>
>
>--
>Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx
>Pervasive Software http://pervasive.com work: 512-231-6117
>vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
