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

--
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(at)masergy(dot)com>
To: "PostgreSQL General" <pgsql-general(at)postgresql(dot)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(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

--
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(at)masergy(dot)com>
> To: "PostgreSQL General" <pgsql-general(at)postgresql(dot)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(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
>
>
>
> --
> 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(at)pervasive(dot)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(at)pervasive(dot)com>
To: "Bob Pawley" <rjpawley(at)shaw(dot)ca>
Cc: "Guy Rouillier" <guyr(at)masergy(dot)com>; "PostgreSQL General"
<pgsql-general(at)postgresql(dot)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(at)masergy(dot)com>
>> To: "PostgreSQL General" <pgsql-general(at)postgresql(dot)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(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
>>
>>
>>
>> --
>> 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(at)pervasive(dot)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(at)pervasive(dot)com>
> To: "Bob Pawley" <rjpawley(at)shaw(dot)ca>
> Cc: "Guy Rouillier" <guyr(at)masergy(dot)com>; "PostgreSQL General"
> <pgsql-general(at)postgresql(dot)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(at)masergy(dot)com>
> >>To: "PostgreSQL General" <pgsql-general(at)postgresql(dot)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(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
> >>
> >>
> >>
> >>--
> >>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(at)pervasive(dot)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(at)pervasive(dot)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(at)pervasive(dot)com>
To: "Bob Pawley" <rjpawley(at)shaw(dot)ca>
Cc: "Guy Rouillier" <guyr(at)masergy(dot)com>; "PostgreSQL General"
<pgsql-general(at)postgresql(dot)org>
Sent: Monday, November 28, 2005 4:13 PM
Subject: Re: [GENERAL] Group By?

> 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(at)pervasive(dot)com>
>> To: "Bob Pawley" <rjpawley(at)shaw(dot)ca>
>> Cc: "Guy Rouillier" <guyr(at)masergy(dot)com>; "PostgreSQL General"
>> <pgsql-general(at)postgresql(dot)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(at)masergy(dot)com>
>> >>To: "PostgreSQL General" <pgsql-general(at)postgresql(dot)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(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
>> >>
>> >>
>> >>
>> >>--
>> >>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(at)pervasive(dot)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(at)pervasive(dot)com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461