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

On Mon, Nov 21, 2005 at 15:53:15 -0800,
Bob Pawley <rjpawley@shaw.ca> 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?

From your description I think it is more likely you want to use subselects.

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.

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

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.

Show quoted text

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

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@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?

Show quoted text

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

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

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

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Yes I am trying to insert all valves into the same row as their associated
mon.

Bob
----- Original Message -----
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Guy Rouillier" <guyr@masergy.com>; "PostgreSQL General"
<pgsql-general@postgresql.org>
Sent: Monday, November 28, 2005 2:18 PM
Subject: Re: [GENERAL] Group By?

Show quoted text

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
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

Try this (untested):

INSERT INTO auto_control( monitor, valve )
SELECT m.device_id, v.device_id
FROM control m
JOIN control v ON (m.association = v.association)
;

On Mon, Nov 28, 2005 at 03:41:53PM -0800, Bob Pawley wrote:

Yes I am trying to insert all valves into the same row as their associated
mon.

Bob
----- Original Message -----
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Guy Rouillier" <guyr@masergy.com>; "PostgreSQL General"
<pgsql-general@postgresql.org>
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@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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
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

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Thank you I'll give it a try.

Bob
----- Original Message -----
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Guy Rouillier" <guyr@masergy.com>; "PostgreSQL General"
<pgsql-general@postgresql.org>
Sent: Monday, November 28, 2005 4:13 PM
Subject: Re: [GENERAL] Group By?

Show quoted text

Try this (untested):

INSERT INTO auto_control( monitor, valve )
SELECT m.device_id, v.device_id
FROM control m
JOIN control v ON (m.association = v.association)
;

On Mon, Nov 28, 2005 at 03:41:53PM -0800, Bob Pawley wrote:

Yes I am trying to insert all valves into the same row as their
associated
mon.

Bob
----- Original Message -----
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Guy Rouillier" <guyr@masergy.com>; "PostgreSQL General"
<pgsql-general@postgresql.org>
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@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

---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
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

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461