Skip site navigation (1) Skip section navigation (2)

Collapsing (select) row values into single text field.

From: Allan Kamau <allank(at)sanbi(dot)ac(dot)za>
To: pgsql-sql(at)postgresql(dot)org
Subject: Collapsing (select) row values into single text field.
Date: 2008-12-10 12:46:33
Message-ID: 493FBA29.9060508@sanbi.ac.za (view raw or flat)
Thread:
Lists: pgsql-sql
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.

Responses

pgsql-sql by date

Next:From: Bruce MomjianDate: 2008-12-10 13:33:29
Subject: Re: inconsistent automatic casting between psql and function
Previous:From: A. KretschmerDate: 2008-12-10 12:33:56
Subject: Re: unique constraint on views

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group