From: | Scott Goodwin <scott(at)scottg(dot)net> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Merging multiple values into comma-delimited list in a view |
Date: | 2004-03-03 20:25:18 |
Message-ID: | E38CD2FD-6D50-11D8-AE0F-000A95A0910A@scottg.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Need some help with the following example. I have email, people and
people2email tables. The people2email table is a one-to-many with one
person able to have many email addresses, and the people_with_email
view ties it all together for me. Here's the output when I do a "select
* from people_with_email;"
first_name | last_name | email_address
------------+------------+--------------------------
Scott | Goodwin | scott(at)scottg(dot)tv
Fred | Flintstone | fred(dot)flintstone(at)blah(dot)com
Barney | Rubble | barney(at)hodown(dot)com
Barney | Rubble | barney(dot)rubble(at)hey(dot)org
What I really want is one person per row, with the email addresses
concat'd together with commas, like this:
first_name | last_name | email_address
------------+------------+--------------------------
Scott | Goodwin | scott(at)scottg(dot)tv
Fred | Flintstone | fred(dot)flintstone(at)blah(dot)com
Barney | Rubble | barney(at)hodown(dot)com, barney(dot)rubble(at)hey(dot)org
My question is: how do I modify the select statement that generates the
people_with_email view so that it generates the output I want?
I'll gladly tie myself to any PG-specific feature that does the job as
I'll not be moving to any other database software within my lifetime if
I can help it (and I can:). I wouldn't mind using arrays, but can't
really change the data type of a column in a view (is there a way to
CAST it?). Might be able to use a materialized view, which could then
support array columns, but I'd be satisfied with a plain text string
that I can split on with Tcl.
The datamodel, with the view and dummy data is below.
thanks,
/s.
======== data model =========
drop view people_with_email;
drop table people2email;
drop table email;
drop table people;
create table email (
email_id integer primary key,
email_address varchar(128) not null unique
);
copy email from stdin with delimiter '|';
1|scott(at)scottg(dot)tv
2|fred(dot)flintstone(at)blah(dot)com
3|barney(at)hodown(dot)com
4|barney(dot)rubble(at)hey(dot)org
\.
create table people (
person_id integer primary key,
first_name varchar(32) not null,
last_name varchar(32) not null
);
copy people from stdin with delimiter '|';
1|Scott|Goodwin
2|Fred|Flintstone
3|Barney|Rubble
\.
create table people2email (
person_id integer references people (person_id),
email_id integer references email (email_id)
);
copy people2email from stdin with delimiter '|';
1|1
2|2
3|3
3|4
\.
create view people_with_email as
select
a.first_name,
a.last_name,
b.email_address
from
people a
inner join
people2email r
on
a.person_id = r.person_id
inner join
email b
on
b.email_id = r.email_id
;
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2004-03-03 22:01:42 | Re: Merging multiple values into comma-delimited list in a view |
Previous Message | M. Bastin | 2004-03-03 17:32:34 | system tables |