pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTranspose rows to columns
4+ messages / 4 participants
[nested] [flat]
* Transpose rows to columns
@ 2004-01-13 01:12 David Witham <davidw@unidial.com.au>
0 siblings, 3 replies; 4+ messages in thread
From: David Witham @ 2004-01-13 01:12 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a query that returns data like this:
cust_id cust_name month cost revenue margin
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
I want to turn it around so it displays like this:
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
(I've used commas to shorten the layout for the example)
Does anyone have some ideas on how to do this?
Thanks,
David Witham
Telephony Platforms Architect
Unidial
Ph: 03 8628 3383
Fax: 03 8628 3399
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Transpose rows to columns
@ 2004-01-13 03:50 Michael Glaesemann <grzm@myrealbox.com>
parent: David Witham <davidw@unidial.com.au>
2 siblings, 0 replies; 4+ messages in thread
From: Michael Glaesemann @ 2004-01-13 03:50 UTC (permalink / raw)
To: David Witham <davidw@unidial.com.au>; +Cc: pgsql-sql
Hi David
On Jan 13, 2004, at 10:12 AM, David Witham wrote:
> Hi,
>
> I have a query that returns data like this:
>
> cust_id cust_name month cost revenue margin
> 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
>
> I want to turn it around so it displays like this:
>
> 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
>
> (I've used commas to shorten the layout for the example)
>
> Does anyone have some ideas on how to do this?
I'd suggest looking at tablefunc in /contrib. It includes crosstab
functionality that you might find useful. I don't think it'll do
exactly what you describe here, but something quite similar.
Michael Glaesemann
grzm myrealbox com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Transpose rows to columns
@ 2004-01-13 15:46 Luis C. Ferreira (aka lcf) <lferreira@tuxedo.com.ar>
parent: David Witham <davidw@unidial.com.au>
2 siblings, 0 replies; 4+ messages in thread
From: Luis C. Ferreira (aka lcf) @ 2004-01-13 15:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Transpose rows to columns
@ 2004-01-14 21:03 Luis C. Ferreira (aka lcf) <lcf@tuxedo.com.ar>
parent: David Witham <davidw@unidial.com.au>
2 siblings, 0 replies; 4+ messages in thread
From: Luis C. Ferreira (aka lcf) @ 2004-01-14 21:03 UTC (permalink / raw)
To: David Witham <davidw@unidial.com.au>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-01-14 21:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-13 01:12 Transpose rows to columns David Witham <davidw@unidial.com.au>
2004-01-13 03:50 ` Michael Glaesemann <grzm@myrealbox.com>
2004-01-13 15:46 ` Luis C. Ferreira (aka lcf) <lferreira@tuxedo.com.ar>
2004-01-14 21:03 ` Luis C. Ferreira (aka lcf) <lcf@tuxedo.com.ar>
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