Equivalent of ValueList function in SQL

and if you were using MySQL, you could have used built-in GROUP_CONCAT()
function...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

> I want to do a query such that the colors are aggregated as a list.

If it _must_ be done in sql, there are some database options. Such as the one mentioned above. There are also some interesting approaches using xml path and cross apply with MS SQL 2005. I do not know about other databases.

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Bear in mind there are some performance implications with all of the methods. For example, a udf would execute once for each name. So the more records, the greater the impact.

Another possibility is to use cfoutput's group attribute to create a list for each name. Assuming that is feasible ..

Thanks for the tip!

Min

You could write a user defined function to create a comma delimited list then use that function in your query.

How to create a comma delimited list in SQL
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

Note this applies to Microsoft SQL Server.