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
