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

help / color / mirror / Atom feed
From: Luis C. Ferreira (aka lcf) <lferreira@tuxedo.com.ar>
To: pgsql-sql@postgresql.org
Subject: Re: Transpose rows to columns
Date: Tue, 13 Jan 2004 12:46:35 -0300
Message-ID: <200401131246.35660@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)

-- 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 Carlos Ferreira





view thread (4+ messages)  latest in thread

Message-ID: <200401131246.35660@tu.numero.de.suerte.17>
Permalink:  ../200401131246.35660@tu.numero.de.suerte.17/
Also on:    postgresql.org/message-id/200401131246.35660@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: lferreira@tuxedo.com.ar
  Subject: Re: Transpose rows to columns
  In-Reply-To: <200401131246.35660@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