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.
