On Tue, 2003-05-27 at 14:19, Richard Huxton wrote: > On Tuesday 27 May 2003 5:34 pm, Chadwick Rolfs wrote: > > So, I have the same problem, but I need all authors for each publication > > to show up in it's own column. I tried the full join query from a > > suggestion off pgsql-sql, but it only returns ONE author id TWICE instead > > of ALL authors at once. > > > > I'll do some RTFMing of the joins.. and post any results I get > > > > BUT, right now, looping over each publication with php isn't taking that > > long. I would like to know how to make this query, though! > > > > Please let me know how to get a result like: > > ____________________________________________________ > > > > |All Authors|Title|Source|Year|Type|Length|Keywords| > > Well, if you search the archives for terms "text", "concat", "aggregate" you > should come up with one solution. This involves writing your own aggregate > function, like SUM() but for text. Don't worry, it's not difficult. The only > issue is that you won't be able to guarantee the order of authors in the > field. If order is required: SELECT custom_aggregate(author) as authors FROM (SELECT author FROM table ORDER BY author) AS tab; The above should give you authors in alphabetical order if custom_aggregate() was written to concatenate text. -- Rod Taylor <rbt@xxxxxx> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment:
signature.asc
Description: This is a digitally signed message part
