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

Re: SQL query help - online music shop - labels & styles

From: Manuel Sugawara <masm(at)fciencias(dot)unam(dot)mx>
To: Nabil Sayegh <nsmail(at)sayegh(dot)de>
Cc: Oliver Beddows <oliver-b(at)ntlworld(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: SQL query help - online music shop - labels & styles
Date: 2001-05-18 16:22:00
Message-ID: m3bsoqvd93.fsf@dep1.fciencias.unam.mx (view raw or flat)
Thread:
Lists: pgsql-novice
> > 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.

In response to

pgsql-novice by date

Next:From: Sandro DentellaDate: 2001-05-19 08:40:02
Subject: ANNOUNCE: tksql (program) & sdsql (Tcl/Tk package)
Previous:From: Peter AsemannDate: 2001-05-18 16:03:25
Subject: inserting values like in mySQL

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