Re: Transpose rows to columns

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, 7991235, 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