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
