1-multiple to string

Hello all,

I'm using SS 2000 and NT4.

Say I've got three tables: T1, T2 and T3. T3 contains the 1-to-multiple data
of the relation between T1 and T2:

------------------------------------------------
CREATE TABLE #T1(
T1PK INTEGER NOT NULL PRIMARY KEY,
Name VARCHAR(3) NOT NULL
)

CREATE TABLE #T2(
T2PK CHAR NOT NULL PRIMARY KEY,
Qty INTEGER NOT NULL
)

CREATE TABLE #T3(
T1PK INTEGER NOT NULL,
T2PK CHAR NOT NULL,
)

ALTER TABLE #T3 ADD CONSTRAINT cPK PRIMARY KEY (T1PK, T2PK)
ALTER TABLE #T3 ADD CONSTRAINT T1FK FOREIGN KEY REFERENCES #T1.T1PK
ALTER TABLE #T3 ADD CONSTRAINT T2FK FOREIGN KEY REFERENCES #T2.T2PK

INSERT INTO #T1 (T1PK, Name)
SELECT 1, 'Bob' UNION
SELECT 2, 'Joe' UNION
SELECT 3, 'Bla'

INSERT INTO #T2 (T2PK, Qty)
SELECT 'A', 3 UNION
SELECT 'B', 2 UNION
SELECT 'C', 1

INSERT INTO #T3 (T1PK, T2PK)
SELECT '1', 'A' UNION
SELECT '1', 'B' UNION
SELECT '2', 'A' UNION
SELECT '2', 'B' UNION
SELECT '2', 'C' UNION
SELECT '3', 'B'

------------------------------------------------

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?

Thanks for your time.

Yannick

re: 1-multiple to string

Yannick Turgeon (nobody@nowhere.com) writes:[color=blue]
> 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?[/color]

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/techinf...2000/books.asp

re: 1-multiple to string

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...[color=blue]
> Yannick Turgeon (nobody@nowhere.com) writes:[color=green]
> > 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?[/color]
>
> 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/techinf...2000/books.asp[/color]

re: 1-multiple to string

Check out method #4 in this post:
http://groups.google.com/groups?selm...TNGP09.phx.gbl

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. You don't have
to write out to a temp table, and you can join against the result. I think
the max length of the output string is 8K, but that is a LOT of concatenated
stuff. I don't know how this function will work with a lot of data, but it
seems pretty neat.

Thanks,

Bill Mac


"Yannick Turgeon" <nobody@nowhere.com> wrote in message
news:11Onc.7089$FH5.381757@news20.bellglobal.com.. .[color=blue]
> Erland,
>
> So I cannot do that. That's what I thought but I was hopping for ...[/color]
humm...[color=blue]
> 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...[color=green]
> > Yannick Turgeon (nobody@nowhere.com) writes:[color=darkred]
> > > 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?[/color]
> >
> > 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/techinf...2000/books.asp[/color]
>
>[/color]

re: 1-multiple to string

Bill MacLean (bsmacleanpam@att.net) writes:[color=blue]
> 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.[/color]

But it does use iteration.

It is of course a neat way to package the iteration, however.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp