Problem solve Get help with specific problems with your technologies, process and projects.

The Sybase list() aggregate function

Pertaining to your answer A list of IDs in a column (March 14, 2003), I agree with you completely. In my database I do have correct design, but unfortunately I need to create a list on the query output. Using the same example I need to create a query that will display:

 id name holdings 71 J.Smith bonds=1047.00 72 B.White stock=937.21, cash=211.03 73 K.Baker bonds=3700.00, cash=42.98 stock=2044.65 ...

In my case the cardinality of the foreign key can be 0..40, so concatenation of subquery results would be too ugly if possible at all. Is there any way to make this query in SQL?

No, not unless you include cursors, which I always mentally assign to the programming language side of the fence and not the SQL side. If you don't want to join the table to itself 40 times (and who would?), then just run a simple query with an ORDER BY, and do current/previous logic using a cursor or by looping over the result set in your programming language.

Unless you're on Sybase. Sybase Adaptive Server Anywhere (but not Adaptive Server Enterprise) has this really neat, albeit proprietary, non-standard aggregate function called list(). What it does is produce a comma-separated list of all the non-null values in a column for each group in a GROUP BY query. So your query would be:

 select id, name, list(holding) from yourtable group by id, name

Gorgeous, isn't it?

Dig Deeper on Oracle database design and architecture