Q
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?


This was last published in September 2003

Dig Deeper on Oracle database design and architecture

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close