1-multiple to string

[Yannick Turgeon]

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

[Erland Sommarskog]

Yannick Turgeon (no****@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, so****@algonet.se

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

[Yannick Turgeon]

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" <so****@algonet.se> wrote in message
news:Xn*********************@127.0.0.1...

Yannick Turgeon (no****@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, so****@algonet.se

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

[Bill MacLean]

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" <no****@nowhere.com> wrote in message
news:11*******************@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" <so****@algonet.se> wrote in message
news:Xn*********************@127.0.0.1...

Yannick Turgeon (no****@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, so****@algonet.se

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

[Erland Sommarskog]

Bill MacLean (bs**********@att.net) writes:

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.

But it does use iteration.

It is of course a neat way to package the iteration, however.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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