Another option is to:
1. do a string replace to make the list into a list of xml values by doing like: "<somexml><item>" + replace(value, ",","</item><item>") + "</item></somexml>" 2. Use the xpath functions build into sql to then query the nodes. 3. Profit. Should work, and would give you more query options over the list. You could create a view Around part 1, that just always has the xml field and query it to your hearts content... On Fri, Aug 5, 2011 at 10:09 AM, GMoney <[email protected]> wrote: > > awesome, thanks jerry i'll take a look at this. The list is in random > order. > > On Fri, Aug 5, 2011 at 9:06 AM, Jerry Barnes <[email protected]> wrote: > > > > > It looks like the list has a random order. Is that the case or is it in > > alphabetical order? Do you use user defined functions? > > > > Regardless, check this out: > > http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows > > > > The key is the following: > > > > *SELECT Author, > > NullIf(SubString(',' + Phrase + ',' , ID , CharIndex(',' , ',' + > > Phrase + ',' , ID) - ID) , '') AS Word > > FROM Tally, Quotes > > WHERE ID <= Len(',' + Phrase + ',') AND SubString(',' + Phrase + ',' , > > ID - 1, 1) = ',' > > AND CharIndex(',' , ',' + Phrase + ',' , ID) - ID > 0* > > > > > > > > From this template it looks like you could create a view with a column > for > > each value and then use a statement similar to this that groups by value > > and > > returns count. > > > > > > > > > > J > > > > - > > > > Ninety percent of politicians give the other ten percent a bad > reputation. > > - > > Henry Kissinger > > > > Politicians are people who, when they see light at the end of the tunnel, > > go > > out and buy some more tunnel. - John Quinton > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-community/message.cfm/messageid:341286 Subscription: http://www.houseoffusion.com/groups/cf-community/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-community/unsubscribe.cfm
