Thread: 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.

Thanks Filip for the User Defined Aggregates information I will look
into it, and compare its performance with the another probable
solution (explained next).
I do recall making use of arrays (then array_to_string()) to do this
kind of collapsing as a subquery in the select clause of a group by
query, but I can not recall the actual syntax.

Allan.

On Wed, Dec 10, 2008 at 4:45 PM, Filip Rembiałkowski
<filip.rembialkowski@gmail.com> wrote:
>
>
> 2008/12/10 Allan Kamau <allank@sanbi.ac.za>
>>
>> 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.
>
>
> sounds like you need a custom aggregate function.
> http://www.postgresql.org/docs/current/static/xaggr.html
>
> however it's not clear how you want to aggregate; what does your actual
> grouping function do?
>
> general pattern is:
>
> CREATE FUNCTION varbit_concat(varbit,varbit)
> returns varbit
> as 'whatever you need' language 'of your choice' immutable;
>
> CREATE AGGREGATE agg_varbit_concat ( varbit ) (
>     SFUNC = varbit_concat,
>     STYPE = varbit
> -- check CREATE AGGREGATE syntax, maybe you need something fancy here
> );
>
>
> -- and finally:
>
> SELECT
>  location,
>  min(lowest_temp) as lowest_overall_temp,
>  agg_varbit_concat(location_bit_data) as overall_location_bit_data
> FROM temp;
>
>
> --
> Filip Rembiałkowski
>