Hi
I am trying to work out if this is possible in a select query
I have a group by query which could result in several rows, what I want to do is do a text equivalent of a sum() eg:
SELECT sum(inv_id),date,cust from invoice
group by date,cust
Is there any way to get to a single concatenated inv_id field with just one row?
Yes, you can define your own aggregate. For example, I did this the other day:
/*
Custom aggregate
This aggregate is so we can aggregate text into paragraph blocks
*/
CREATE OR REPLACE FUNCTION join_paras(text, text) RETURNS text AS
'
SELECT CASE
WHEN ($1 = '''') THEN $2
ELSE $1 || ''\n'' || $2
END;
' LANGUAGE 'SQL' IMMUTABLE;CREATE AGGREGATE agg_paras (sfunc1=join_paras, basetype=text, stype1=text, initcond1='');
Note I defined my own text-concatenation function because I wanted to insert newlines between each block of text. If you just wanted joined text you could use the built-in textcat()
Full specs of create aggregate are in the manuals.
HTH -- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
