Re: Collapsing (select) row values into single text field.

Hi all,
I would like to concatenate the field values of several rows in a table that meet some similarity criteria  based on a the values of  some other field (more like a group by). Then I would also like to also include the lowest value of another associated field along.

I have a table that contains 3 fields of interest.
create table temp
(id INTEGER NOT NULL
,location TEXT NOT NULL --this will hold the zip code
,lowest_temp NUMERIC(5,2) NOT NULL --The lowest temperature at some given night
,location_bit_data VARBIT NOT NULL
,PRIMARY KEY(id)
);

There will be usually more than one record for a location (location+lowest_temp is not unique either).
Now I would like to collapse the data in this table (an populate another table) as follows.
Lets assume this table has the structure below.

create table temp_major
(id INTEGER NOT NULL
,location TEXT NOT NULL --this will hold the zip code
,lowest_overall_temp NUMERIC(5,2) NOT NULL --The lowest temperature at some given night
,overall_location_bit_data VARBIT NOT NULL
,PRIMARY KEY(id)
,UNIQUE(location)
);

The new table (temp_major) is population as follows: the "location_bit_data" values for a given location are "grouped" into one entry (to create a concatenation effect), the lowest_temp reading across all the records of the given location is noted and the location is also noted, this data is used in populating the table.

The solution I have so far involves using a stored procedure and cursors (on Select .. order by location) to continuously "grow" the data for a given location's "overall_location_bit_data" field.

Allan.