What there are others!!! :-)) ______________________________________________________
Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX: 301.424.3696 649 North Horners Lane E-Mail: [EMAIL PROTECTED] Rockville, MD 20850-1299 WWW: http://www.euservices.com ______________________________________________________ > -----Original Message----- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 06, 2003 9:08 AM > To: CF-Talk > Subject: RE: SQL: Brain Teaser > > > great for DBs which support t-sql stored procedures :-) > > -----Original Message----- > From: Bill Grover [mailto:[EMAIL PROTECTED] > Sent: 06 August 2003 14:05 > To: CF-Talk > Subject: RE: SQL: Brain Teaser > > > If you wanted to do it all in SQL you can try the following. > I would write > it as a stored procedure and call that but you might be able > to do it in a > cfquery. The trick is that if you select into a variable and > the select > statement will have multiple rows the variable assignment > happens for each > row so you can effectively append to the variable. > > declare @lnUserid int, > @lcUserName varchar(50), > @lcComments varchar(8000), > @lrFinalComments table (userid int, > username varchar(50), > comments varchar(8000)) > > declare UserList cursor fast_forward > for select userid, username > from users > > open UserList > > fetch next from UserList into @lnUserid, @lcUserName > > while @@fetch_status = 0 > begin > set @lcComments = '' > select @lcComments = @lcComments + comment + '<br>' > from comments > where userid = @lnUserid > insert into @lrFinalComments > values (@lnUserid, > @lcUserName, > left(@lcComments, len(@lcComments) - 4)) > fetch next from UserList into @lnUserid, @lcUserName > end > > select * from @lrFinalComments > > ______________________________________________________ > > Bill Grover > Supervisor MIS Phone: 301.424.3300 x3324 > EU Services, Inc. FAX: 301.424.3696 > 649 North Horners Lane E-Mail: [EMAIL PROTECTED] > Rockville, MD 20850-1299 WWW: http://www.euservices.com > ______________________________________________________ > > > > > -----Original Message----- > > From: Tyler Clendenin [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, August 06, 2003 8:58 AM > > To: CF-Talk > > Subject: RE: SQL: Brain Teaser > > > > > > That would do it but not from sql. I know really don't like > > having to use > > cfoutput to group I wish they would add group to cfloop at > > least then it > > would be a little easier. > > > > Tyler Clendenin > > GSL Solutions > > > > -----Original Message----- > > From: Tony Schreiber [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, August 06, 2003 7:46 AM > > To: CF-Talk > > Subject: RE: SQL: Brain Teaser > > > > > > USERS TABLE > > > > ----------------- > > > > USER_ID > > > > USERNAME > > > > etc... > > > > > > > > COMMENTS TABLE > > > > ------------------------- > > > > COMMENT_ID > > > > USER_ID > > > > COMMENT > > > > etc... > > > > > > > > I know I can query USERS and then loop through the > > results fetching > > > > the comments... but I'd rather not do that. Here's the > > results I'm > > > > trying to get... in a SINGLE query if possible: > > > > > > > > USER_ID,USERNAME,MERGED_COMMENTS > > > > 1,joe,comment1<br>comment2<br>comment3 > > > > 2,sam,comment > > > > 3,sue,comment<br>another comment<br>etc. > > > > <CFQUERY NAME="mergedcomments"> > > SELECT username, comment > > FROM users u, comments c > > WHERE u.user_id = c.userid > > ORDER BY username > > </CFQUERY> > > > > <CFOUTPUT QUERY="mergedcomments" GROUP="username"> > > #username#,<CFOUTPUT>#comment#<BR></CFOUTPUT> > > </CFOUTPUT> > > > > Wouldn't that do it? > > > > Tony Schreiber, Senior Partner > > Man and Machine, Limited > > mailto:[EMAIL PROTECTED] > http://www.technocraft.com > > http://www.is300.net The Enthusiast's Home of the Lexus IS300 > since 1999 > ***** PARTS STORE NOW OPEN ***** http://www.is300.net/store/ > http://www.simplemessageboard.com Free Forum Software for > Cold Fusion > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
