From: Harald Fuchs <hf0731x@protecting.net>
To: pgsql-sql@postgresql.org
Subject: Re: Need a SQL to create sets of hobbies
Date: 20 Sep 2006 13:33:01 +0200
Message-ID: <puhcz2kc2a.fsf@srv.protecting.net> (raw)
References: <1158729519.6421.271361727@webmail.messagingengine.com>
In article <1158729519.6421.271361727@webmail.messagingengine.com>,
"CN" <cnliou9@fastmail.fm> writes:
> select * from x1;
> name | hobby
> -------+----------
> John | music
> John | arts
> Bob | arts
> Bob | music
> Rocky | computer
> Steve | arts
> Steve | football
> Tom | computer
> Tom | music
> (9 rows)
> Now we have 4 sets of hobbies:
> set 1: music, arts
> set 2: computer
> set 3: arts, football
> set 4: computer, music
> I am looking for an SQL that creates sets of hobbies in table x2 by
> selecting from table x1:
> CREATE TABLE x2 (sid INTEGER, hobby TEXT, PRIMARY KEY(sid,hobby));
> and makes x2 contain rows:
> sid | hobby
> -------+----------
> 1 | music
> 1 | arts
> 2 | computer
> 3 | arts
> 3 | football
> 4 | computer
> 4 | music
> where gid starts from 1.
You could use something like that:
CREATE TEMP TABLE tmp (
id SERIAL NOT NULL,
name TEXT NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO tmp (name)
SELECT DISTINCT ON (ARRAY (
SELECT y1.hobby
FROM x1 y1
WHERE y1.name = y2.name
ORDER BY y1.name, y1.hobby
))
y2.name
FROM x1 y2;
INSERT INTO x2 (sid, hobby)
SELECT tmp.id, x1.hobby
FROM tmp
JOIN x1 ON x1.name = tmp.name;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: hf0731x@protecting.net
Subject: Re: Need a SQL to create sets of hobbies
In-Reply-To: <puhcz2kc2a.fsf@srv.protecting.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox