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.