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)
-- 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
