I have a table with three fields (giver, taker, amount)
I have another table of people with two fields (id, name)

The fields "giver" and "taker" are match the values of "id"

I'd like to make a table (I've heard it called a distance matrix, but
this one's a little different because it's no symmetrical along the
major axis) using SQL (I _could_ do it with an external language, but
would prefer straight SQL).

So let's say in the id/name table I've got:
1 | Jim
2 | Bob
3 | James

And in the giver/taker/amount table I've got (note that there's
multiple giver-taker entries)
giver | taker | amount
1 | 2 | 20
1 | 3 | 10
1 | 2 | 8
2 | 1 | 18
2 | 1 | 5
2 | 3 | 70
3 | 1 | 64

I would like an SQL query that produces the following (the columns
being dynamic -- is that even possible?):
Giver | toJim | toBob | toJames
Jim | 0 | 28 | 10
Bob | 23 | 0 | 70
James | 64 | 0 | 0

Is this possible with SQL

HaggMan wrote:
This is a cross tab query. it is not really doable in SQL to arbitrary
number of "taker"s.
You will need to create views or subqueries for each taker.

Do it procedurally. you'll be much happier.
ed

HaggMan wrote:
It is easy to prepare some data using SQL that can then be postprocessed
with a very small snipplet of handwritten code.

SELECT SUM(amount), giver, taker
FROM givertaker
GROUP BY giver, taker
ORDER BY giver, taker

Join to the table with names, and all the code have to do is output one
cell at a time from right to left.

Regards,

Michael.
--
Which is more dangerous? TV guided missiles or TV guided families?
I am less likely to answer usenet postings by anonymous authors.
Visit my home page at http://michael.zedeler.dk/

If it is not neccessary to separate columns, only the result of looking
is like your example is enough:
Giver | toJim | toBob | toJames
Jim | 0 | 28 | 10
Bob | 23 | 0 | 70
James | 64 | 0 | 0
You can use recursive query. (or XML functions on DB2)
Followings are examples on DB2.

------------------------------ Commands Entered
------------------------------
WITH
Distances (giver, taker, distance) AS (
SELECT id1.id, id2.id, CHAR(SUM(amount))
FROM
id_name id1
INNER JOIN
id_name id2
ON 0=0
LEFT OUTER JOIN
giver_taker gtr
ON id1.id = gtr.giver
AND id2.id = gtr.taker
GROUP BY id1.id, id2.id
UNION ALL
SELECT 0, id, name
FROM id_name
)
,Recurse (seq, id, giver, distances) AS (
(SELECT 0, id, CHAR(name,8), CAST('' AS VARCHAR(50))
FROM id_name
UNION ALL
VALUES (0, 0, 'Giver ', CAST('' AS VARCHAR(50)) )
)
UNION ALL
SELECT pre.seq +1
, pre.id
, pre.giver
, pre.distance ||
CASE pre.id
WHEN 0 THEN
'| to ' || CHAR(idn.name,7)
ELSE
'| ' || CHAR(COALESCE(new.distance,'0'),10)
END
FROM Recurse pre
, Distances new
, id_name idn
WHERE pre.seq < 1000
AND new.giver = pre.id
AND new.taker = pre.seq + 1
AND idn.id = pre.seq + 1
)
SELECT giver
, distances
FROM Recurse
WHERE seq = (SELECT MAX(seq) FROM Recurse)
ORDER BY id;
------------------------------------------------------------------------------

GIVER DISTANCES
-------- --------------------------------------------------
Giver | to Jim | to Bob | to James
Jim | 0 | 28 | 10
Bob | 23 | 0 | 70
James | 64 | 0 | 0

4 record(s) selected.



------------------------------ Commands Entered
------------------------------
SELECT MAX(name) AS giver
, SUBSTR(REPLACE(REPLACE(
XMLSERIALIZE(CONTENT XMLAGG(
XMLELEMENT(NAME c, CHAR(COALESCE(distance,'0'),8))
ORDER BY taker)
AS VARCHAR(100)), '<C>', '| '), '</C>', '')
, 1, 50) as distances
FROM (
SELECT id1.id, id2.id, CHAR(SUM(amount))
FROM
id_name id1
INNER JOIN
id_name id2
ON 0=0
LEFT OUTER JOIN
giver_taker gtr
ON id1.id = gtr.giver
AND id2.id = gtr.taker
GROUP BY id1.id, id2.id
UNION ALL
SELECT 0, id, 'to ' || name
FROM id_name
) AS Distances (giver, taker, distance)
INNER JOIN
(SELECT *
FROM id_name
UNION ALL
VALUES (0, 'Giver')
) AS idn (id, name)
ON id = giver
GROUP BY
giver, id
ORDER BY id;
------------------------------------------------------------------------------

GIVER DISTANCES
---------- --------------------------------------------------
Giver | to Jim | to Bob | to James
Jim | 0 | 28 | 10
Bob | 23 | 0 | 70
James | 64 | 0 | 0

4 record(s) selected.