Обсуждение: SQL query help - online music shop - labels & styles

hello,

I`m currently developing an online music shop, mainly as a learning
experience.  And I`m having a few problems regarding, querying and displaying
records labels and their associated styles of music.

Say I have the following tables and sequences :
-------------------------------
create table label
(id integer not null,
 name text not null,
 primary key(id));

create sequence label_id_seq increment 1 start 1;

create table label_style
(label_id integer not null,
 style_id smallint not null);

create sequence style_id_seq increment 1 start 1;

create table style
(id smallint not null,
 name text not null,
 primary key(id));

I then insert a some styles of music:
------------------------
insert into style values(nextval('style_id_seq'),'Trance');
insert into style values(nextval('style_id_seq'),'Techno');
insert into style values(nextval('style_id_seq'),'House');

I insert a record label:
--------------
insert into label values(nextval('label_id_seq'),'Matsuri Productions');

I associate the some styles of music to that label:
--------------------------------
insert into label_style values(1,1);
insert into label_style values(1,2);
insert into label_style values(1,3);

Now I join all three tables like so:
----------------------
SELECT l.name as label,s.name as style
  FROM label l, style s, label_style ls
 WHERE s.id = ls.style_id
   AND l.id = ls.label_id

Which produces the following:
----------------------
  label                        |  style
----------------------
 Matsuri Productions | House
 Matsuri Productions | Techno
 Matsuri Productions | Trance

BUT! How can I achieve the following??
--------------------------------
  label                        | style1   | style2   | style3  
-------------------------------
 Matsuri Productions | House  | Techno | Trance

What kind of query do I need to use? Am I dreaming here or what?
Any help with this would be GREATLY appreciated.

oliver-b@ntlworld.com

(Nearly forgot - I`m using PostgreSQL v.7.0.3, and SuSE linux 7.1)

On 18 May 2001 13:20:25 +0100, Oliver Beddows wrote:
> Which produces the following:
> ----------------------
>   label                        |  style
> ----------------------
>  Matsuri Productions | House
>  Matsuri Productions | Techno
>  Matsuri Productions | Trance
>
> BUT! How can I achieve the following??
> --------------------------------
>   label                        | style1   | style2   | style3
> -------------------------------
>  Matsuri Productions | House  | Techno | Trance


Several times I asked if there is a SQL-way to transpone.
As I didn't get an answer I believe the answer is no.

If there was such a function it would have to take care of at least the
following:
The contents of the first column of table t has to follow the
conventions for column-names.
Columns 1..n of t have to be of the same type.
...

Perhaps it is possible to write a plpgsql-function for it,
but propably it's not worth the effort.

cu

--
Nabil Sayegh

> > BUT! How can I achieve the following??
> > --------------------------------
> >   label                        | style1   | style2   | style3
> > -------------------------------
> >  Matsuri Productions | House  | Techno | Trance
>
>
> Several times I asked if there is a SQL-way to transpone.
> As I didn't get an answer I believe the answer is no.

[...]

> Perhaps it is possible to write a plpgsql-function for it,
> but propably it's not worth the effort.
>

I faced the same problem a few days ago; my case was pretty easy to
solve: first create a function that join the arguments with, say, a
blank space:

  create function concat(text,text) returns text as 'select (case when
  $1 <> '''' then $1 || '' '' else $1 end) || $2' language 'sql';

create an aggregate function. Something like:

  create aggregate concat_agg (
    basetype = text,
    sfunc = concat,
    stype = text,
    initcond = ''
  );

create a temporal test table and insert some values:

  create table tmp(name text, token text);
  insert into tmp values ('masm','lola');
  insert into tmp values ('masm','dola');
  insert into tmp values ('masm','mola');
  insert into tmp values ('masm','pola');
  insert into tmp values ('jsf','kola');
  insert into tmp values ('jsf','dona');
  insert into tmp values ('jsf','poca');

and then select and group by according to your needs:

regression=# select name,concat_agg(token) from tmp group by name;
 name |     concat_agg
------+---------------------
 jsf  | kola dona poca
 masm | lola dola mola pola
(2 rows)
regression=#

HTH,
Manuel.