Re: SQL query - single text value from group by
От | mike |
---|---|
Тема | Re: SQL query - single text value from group by |
Дата | |
Msg-id | 1094661275.6956.19.camel@datacc обсуждение исходный текст |
Ответ на | Re: SQL query - single text value from group by (Richard Huxton <dev@archonet.com>) |
Список | pgsql-general |
On Wed, 2004-09-08 at 17:13 +0100, Richard Huxton wrote: > mike wrote: > > 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 thanks - as sods law dictates I found a solution just after I posted along the same lines, after a couple of hours of fruitless googling.
В списке pgsql-general по дате отправления: