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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Sandro Dentella | 2001-05-19 08:40:02 | ANNOUNCE: tksql (program) & sdsql (Tcl/Tk package) |
Previous Message | Peter Asemann | 2001-05-18 16:03:25 | inserting values like in mySQL |