> What I want is to convert the relation's multiple side to a string.
> Instead of:
>
> SELECT T1.Name, T2.T2PK, T2.Qty
> FROM #T3 T3
> INNER JOIN #T2 T2 ON T2.T2PK = T3.T2PK
> INNER JOIN #T1 T1 ON T1.T1PK = T3.T1PK
>
> Name T2PK Qty
> ---- ---- -----------
> Bob A 3
> Bob B 2
> Joe A 3
> Joe B 2
> Joe C 1
> Bla B 2
>
> I would like to get:
>
> Name Info
> ---- -----------
> Bob "A-3;B-2"
> Joe "A-3;B-2;C-1"
> Bla "B-2"
>
> Is it possible to do that? How?

"Erland Sommarskog" <sommar@algonet.se> wrote in message
news:Xns94E41C25B6AAYazorman@127.0.0.1...
> Yannick Turgeon (nobody@nowhere.com) writes:
> > What I want is to convert the relation's multiple side to a string.
> > Instead of:
> >
> > SELECT T1.Name, T2.T2PK, T2.Qty
> > FROM #T3 T3
> > INNER JOIN #T2 T2 ON T2.T2PK = T3.T2PK
> > INNER JOIN #T1 T1 ON T1.T1PK = T3.T1PK
> >
> > Name T2PK Qty
> > ---- ---- -----------
> > Bob A 3
> > Bob B 2
> > Joe A 3
> > Joe B 2
> > Joe C 1
> > Bla B 2
> >
> > I would like to get:
> >
> > Name Info
> > ---- -----------
> > Bob "A-3;B-2"
> > Joe "A-3;B-2;C-1"
> > Bla "B-2"
> >
> > Is it possible to do that? How?
>
> In a single statement, no. You could write a cursor for the task to
> accumulate the data in a temp table. You would then find that you would
> have to define a maximum length for that Info column. This gives hint
> why you cannot do this in a query.
>
> You might be better off doing this in client code.
>
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

"Yannick Turgeon" <nobody@nowhere.com> wrote in message
news:11Onc.7089$FH5.381757@news20.bellglobal.com...
> Erland,
>
> So I cannot do that. That's what I thought but I was hopping for ...
humm...
> a miracle! :o) I just created a SP using a cursor and a temp table as you
> suggested. It's not that fast but it does the job. Thanks for your help.
>
> Yannick
>
>
> "Erland Sommarskog" <sommar@algonet.se> wrote in message
> news:Xns94E41C25B6AAYazorman@127.0.0.1...
> > Yannick Turgeon (nobody@nowhere.com) writes:
> > > What I want is to convert the relation's multiple side to a string.
> > > Instead of:
> > >
> > > SELECT T1.Name, T2.T2PK, T2.Qty
> > > FROM #T3 T3
> > > INNER JOIN #T2 T2 ON T2.T2PK = T3.T2PK
> > > INNER JOIN #T1 T1 ON T1.T1PK = T3.T1PK
> > >
> > > Name T2PK Qty
> > > ---- ---- -----------
> > > Bob A 3
> > > Bob B 2
> > > Joe A 3
> > > Joe B 2
> > > Joe C 1
> > > Bla B 2
> > >
> > > I would like to get:
> > >
> > > Name Info
> > > ---- -----------
> > > Bob "A-3;B-2"
> > > Joe "A-3;B-2;C-1"
> > > Bla "B-2"
> > >
> > > Is it possible to do that? How?
> >
> > In a single statement, no. You could write a cursor for the task to
> > accumulate the data in a temp table. You would then find that you would
> > have to define a maximum length for that Info column. This gives hint
> > why you cannot do this in a query.
> >
> > You might be better off doing this in client code.
> >
> >
> > --
> > Erland Sommarskog, SQL Server MVP, sommar@algonet.se
> >
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>
>

> I asked the same question in another thread ( called 1:m database
> relation "Flattened" for reporting purposes) and was given the above
> link. Method #4 uses a table-value function and does not require a
> cursor.