You can create comma lists in T-SQL, if that is what you want to do. T-SQL is the Microsoft-flavor of SQL. I'm not sure if that is what you are using.
So instead of 1 A 1 B 2 A you would get 1 A,B 2 A -Mike Chabot On Tue, Jul 6, 2010 at 1:33 PM, <> wrote: > > Hi, > > There is something I cannot figure a simple way of doing it, either in > SQL or in CF. > > JOINS in SQl are relly handful, but sometimes, they can generate qute > big queries for nothing. > Suppose I have this: > SELECT <table1.many columns>, table2.justOneColumn > FROM table1 > LEFT JOIN table2 ON table1.column1 = table2.column2 > > Suppose that Table2 contains a rather large number of records for each > record in table1. > The result will be a pretty large data set, with most information > repeated in all columns except for justOneColumn, quite a useless overhead. > What would be much more efficient in some instances would be to have > only one row for every record in table1, all rows in the join table > represented by a list. Something like: > SELECT <table1.many columns>, LISTOF(table2.justOneColumn) > FROM table1 > LEFT JOIN table2 ON table1.column1 = table2.column2 > > I do not see anything like this in SQL; > Of course, it could be done in CF using a GROUP in a CFOUTPUT to create > a new Query, but the overhead would be worse. > Any Idea ? > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335122 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
