On 6 14 , 1 04 , BobRoyAce <b...@omegasoftwareinc.com> wrote:
> I am using SQL Server 2005.
>
> Let's say that I have a table called Roes, and another table called
> RoeComments, with a one-to-many relationship. In other words, a single
> record in Roes can have multiple records in RoeComments. For purposes
> of this question, let's say that I only care about one field in
> RoeComments, namely RoeCommentText. Now, suppose I have the following:
>
> SELECT pkRoeID, Name FROM Roes
> 1 Bill
> 2 Bob
> 3 Lisa
>
> SELECT fkRoeID, RoeCommentText FROM RoeComments
> 1 Comment1
> 1 Comment2
> 1 Comment3
> 3 Comment1
>
> Now, what I want to do is combine data from both tables such that I
> get back one record for each record in Roes which has one field, let's
> say called AllRoeComments, which contains any and all associated
> comments from RoeComments concatenated together using "--" characters
> (CRLF) after each one except for the last one. Thus, my result would
> be something like this:
>
> pkRoeID, Name, AllRoeComments
> 1, Bill, Comment1--Comment2--Comment3
> 2, Bob, NULL
> 3, Lisa, Comment1
>
> Worst case, I could have "--" characters after all of them and then
> remove it later in VB code.
>
> Is there a way to do this with a simple query as opposed to a stored
> procedure? If simple query is possible, please show that. If not,
> please show stored procedure code.

On Jun 14, 11:44 am, xyb <xiangyua...@gmail.com> wrote:
> On 6 14 , 1 04 , BobRoyAce <b...@omegasoftwareinc.com> wrote:
>
>
>
>
>
> > I am using SQL Server 2005.
>
> > Let's say that I have a table called Roes, and another table called
> > RoeComments, with a one-to-many relationship. In other words, a single
> > record in Roes can have multiple records in RoeComments. For purposes
> > of this question, let's say that I only care about one field in
> > RoeComments, namely RoeCommentText. Now, suppose I have the following:
>
> > SELECT pkRoeID, Name FROM Roes
> > 1 Bill
> > 2 Bob
> > 3 Lisa
>
> > SELECT fkRoeID, RoeCommentText FROM RoeComments
> > 1 Comment1
> > 1 Comment2
> > 1 Comment3
> > 3 Comment1
>
> > Now, what I want to do is combine data from both tables such that I
> > get back one record for each record in Roes which has one field, let's
> > say called AllRoeComments, which contains any and all associated
> > comments from RoeComments concatenated together using "--" characters
> > (CRLF) after each one except for the last one. Thus, my result would
> > be something like this:
>
> > pkRoeID, Name, AllRoeComments
> > 1, Bill, Comment1--Comment2--Comment3
> > 2, Bob, NULL
> > 3, Lisa, Comment1
>
> > Worst case, I could have "--" characters after all of them and then
> > remove it later in VB code.
>
> > Is there a way to do this with a simple query as opposed to a stored
> > procedure? If simple query is possible, please show that. If not,
> > please show stored procedure code.
>
> try,a little ugly:(
> select pkRoeID, Name,
> (
> select '--'+b.cc2 as "data()"
> from RoeComments as b
> where a.c1 = b.c1
> for xml path('')
> ) as Comment
> from Roes as a- Hide quoted text -
>
> - Show quoted text -

> select pkRoeID, Name,
> stuff((
> select '--'+b.cc2 as "data()"
> from RoeComments as b
> where a.c1 = b.c1
> for xml path('')
> ),1,1,'') as Comment
> from Roes as a
>
> Regards
> Amish Shahhttp://shahamishm.tripod.com

On Jun 14, 10:25 pm, BobRoyAce <b...@omegasoftwareinc.com> wrote:
> > select pkRoeID, Name,
> > stuff((
> > select '--'+b.cc2 as "data()"
> > from RoeComments as b
> > where a.c1 = b.c1
> > for xml path('')
> > ),1,1,'') as Comment
> > from Roes as a
>
> > Regards
> > Amish Shahhttp://shahamishm.tripod.com
>
> Thanks...very helpful, though I must admit I don't understand the part
> that says 'as "data()"'. For my own education, could you explain that
> part?
>
> Also, the query you suggest results in a single '-' in front of the
> first comment and then ' --' in front of every subsequent one...would
> like it if I could get rid of that first '-' in front of the first
> comment (e.g. '-Comment1 --Comment2 --Comment3' would become 'Comment1
> --Comment2 --Comment3'). If can't, not a problem...will just do in VB
> code later...would be cool if there was a way, though.
>
> NOTE: I responded to this post earlier today, but it doesn't seem to
> be showing up. Please forgive me if it ends up showing up, resulting
> in two responses from me.