X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E5CF4D1E7DB for ; Wed, 14 Jan 2004 21:10:44 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 20614-10 for ; Wed, 14 Jan 2004 17:10:15 -0400 (AST) Received: from camel.luzyfuerza.org.ar (sfe194.is.net.ar [200.0.209.194]) by svr1.postgresql.org (Postfix) with SMTP id 61C6BD1DF9C for ; Wed, 14 Jan 2004 17:04:25 -0400 (AST) Received: (qmail 2235 invoked from network); 14 Jan 2004 21:04:05 -0000 Received: from unknown (HELO lcf.luzyfuerza.org.ar) (10.7.17.3) by www.luzyfuerza.org with SMTP; 14 Jan 2004 21:04:05 -0000 From: "Luis C. Ferreira (aka lcf)" To: "David Witham" , Subject: Re: Transpose rows to columns Date: Wed, 14 Jan 2004 18:03:49 -0300 User-Agent: KMail/1.5 References: In-Reply-To: De: Mime (headers) MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit Content-Disposition: inline Message-Id: <200401141803.49814@tu.numero.de.suerte.17> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/123 X-Sequence-Number: 16516 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