I beat my head against this one for a long time a while back. I think Sybase SQL has a LIST function, but MS SQL Server doesn't. I think I solved it by doing a GROUP BY and then collapsing the output in the application. Eric
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 29, 2001 7:38 AM To: SQL Subject: RE: Re: simple JOIN question He's right, you need to get the results in teh first form, then when you are looping through your output, you can check to see if it's the same id, and if so, ptu in same list, if not, go to next list. Travis ---- Original Message ---- From: "Dean H. Saxe" <[EMAIL PROTECTED]> Sent: 2001-11-29 05:10:46.0 To: SQL <[EMAIL PROTECTED]> Subject: Re: simple JOIN question This is not possible in SQL without some additional logic which is available in stored procedures. I believe SQL server has some function to accomplish what you want, as well. -dhs >I've got a simple LEFT JOIN, where i retrieve the UserID and the ServiceID's >of Services that User needs. Since they are realted through a many-2-many >relationship, I get something like > > >UserID ServiceID >1 402 >1 417 >1 533 >2 200 >2 201 >3 {null} > >ok, no problem. What I *REALLY* want in the result set is for the ServiceID >to be turned into a list, something like: > >UserID ServiceIDs >1 402,417,533 >2 200,201 >3 {null} > >so how can I make SQL do >this for me (esp with the {null})? It's gonna turn out to be something easy >that I've seen before, but I haven't had to write something like this for >like a year and now I'm drawing a one in the morning blank. Is it a GROUP BY >thing? or a sub-query thing? > > > >Archives: http://www.mail-archive.com/[email protected]/ >Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists -- Dean H. Saxe [EMAIL PROTECTED] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
