To: pgsql-sql@postgresql.org
From: Harald Fuchs <hf0731x@protecting.net>
Subject: Re: Need a SQL to create sets of hobbies
Date: 20 Sep 2006 13:33:01 +0200
Organization: Linux Private Site
Lines: 66
Message-ID: <puhcz2kc2a.fsf@srv.protecting.net>
References: <1158729519.6421.271361727@webmail.messagingengine.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
User-Agent: Gnus/5.0808 (Gnus v5.8.8) Emacs/20.7

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;