pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Luis C. Ferreira (aka lcf) <lcf@tuxedo.com.ar>
To: David Witham <davidw@unidial.com.au>
To: pgsql-sql@postgresql.org
Subject: Re: Transpose rows to columns
Date: Wed, 14 Jan 2004 18:03:49 -0300
Message-ID: <200401141803.49814@tu.numero.de.suerte.17> (raw)
In-Reply-To: <CFA248776934FD43847E740E43C346D199DB64@ozimelb03.ozicom.com>
References: <CFA248776934FD43847E740E43C346D199DB64@ozimelb03.ozicom.com>

El Lun 12 Ene 2004 22:12, David Witham escribió:
>DW: Hi,
>DW:
>DW: I have a query that returns data like this:
>DW:
>DW: cust_id		cust_name	month		cost	revenue		margin
>DW: 991234		ABC		2003-07-01	10	15		5
>DW: 991234		ABC		2003-08-01	11	17		6
>DW: 991234		ABC		2003-09-01	12	19		7
>DW: 991235		XYZ		2003-07-01	13	21		8
>DW: 991235		XYZ		2003-08-01	12	19		7
>DW: 991235		XYZ		2003-09-01	11	17		6
>DW:
>DW: I want to turn it around so it displays like this:
>DW:
>DW: 991234,ABC,2003-07-01,10,15,5,2003-08-01,11,17,6,2003-09-01,12,19,7
>DW: 991235,XYZ,2003-07-01,13,21,8,2003-08-01,12,19,7,2003-09-01,11,17,6

Hi, the following query

select cust_id || ', ' || cust_name || ', ' || list(month::text || ', ' ||
cost || ', ' || revenue || ', ' || margin) as result from tmp122 group by
cust_id, cust_name;

 *DISPLAYS* data like this:

                                      result
-----------------------------------------------------------------------------
----- 991234, ABC, 2003-07-01, 10, 15, 5, 2003-08-01, 11, 17, 6, 2003-09-01,
 12, 19, 7
 991235, XYZ, 2003-07-01, 13, 21, 8, 2003-08-01, 12, 19, 7, 2003-09-01, 11,
17, 6
(2 rows)

the type 'list' and the function 'comma_cat' (I cannot remember  where I took 
it, but are very useful)...

CREATE FUNCTION comma_cat (text, text) RETURNS text
    AS 'select case
WHEN $2 is null or $2 = '''' THEN $1
WHEN $1 is null or $1 = '''' THEN $2
ELSE $1 || '', '' || $2
END'
    LANGUAGE sql;


CREATE AGGREGATE list (
    BASETYPE = text,
    SFUNC = comma_cat,
    STYPE = text,
    INITCOND = ''
);



-- Original data for test --
drop table tmp122;
create temp table tmp122 (
        cust_id integer,
        cust_name       varchar,
        month           date,
        cost            integer,
        revenue         integer,
        margin          integer
);

copy tmp122 from stdin;
991234  ABC     2003-07-01      10      15      5
991234  ABC     2003-08-01      11      17      6
991234  ABC     2003-09-01      12      19      7
991235  XYZ     2003-07-01      13      21      8
991235  XYZ     2003-08-01      12      19      7
991235  XYZ     2003-09-01      11      17      6
\.


-- 
Chau, Luis



view thread (4+ messages)

Message-ID: <200401141803.49814@tu.numero.de.suerte.17>
Permalink:  ../200401141803.49814@tu.numero.de.suerte.17/
Also on:    postgresql.org/message-id/200401141803.49814@tu.numero.de.suerte.17

 · 

reply
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: lcf@tuxedo.com.ar, davidw@unidial.com.au
  Subject: Re: Transpose rows to columns
  In-Reply-To: <200401141803.49814@tu.numero.de.suerte.17>

* 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