Equivalent of ValueList function in SQL

[Guest]

Hi,
Is there an equivalent of valueList function in SQL? Here's what I am trying to do:

Suppose I have the following table:
Name -- Color
******************
John -- Green
John -- Red
Mike -- White

I want to do a query such that the colors are aggregated as a list. So the result would be:
Name -- Color List
***********************
John -- Green, Red
Mike -- White

The only way I could think of doing this is to loop through each name in the table and doing ValueList in each loop. Is there a better way?

Thanks.

Min

[JR__Bob__Dobbs-qSBHQ2]

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.

[Guest]

Thanks for the tip!

Min

[-__cfSearching__-]

> 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.htm...

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 ..

[Newsgroup_User]

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/