×

Q:

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?

 


 

A:

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
\.