Re: Produce set of sets using SQL

Adam,

If you want to make a pivot table, you may try to use MS Excel. You can
link to the system for the data represented in the first table and create a
pivot table on that data.

Just a thought.

With regards,
Carel Teijgeler

P.S: .BTW, I think you are chasing rabbits.



*********** REPLY SEPARATOR ***********

On 25-7-2008 at 12:37 Adam Glauser wrote:

I am trying to get an idea of how often we have trucks take a partial
load from one terminal to another, and then continue to (an)other
terminal(s) with the rest of the freight. I'm also trying to determine
if certain combinations of terminals are much more common than others.

Here is a simplified example which outlines the problem. Suppose we
have a table like this:

k1 k2 dest
a a 1
a a 2
a b 3
b c 4
b c 5
b c 6

(k1,k2) constitutes a unique identifier for a load, and each load has
one or more destinations. I would like to end up with ((1,2), (3),
(4,5,6)), that is, a set of all combinations of destinations which have
been stops on the same load. I'd then like to count how many loads have
used a particular combination, but that's probably best as a separate

step.

I can use a query like this
select a.k1,a.k2,a.dest, coalesce(b.dest,0), coalesce(c.dest,0)
from testtable a
left outer join testtable b
on a.k1=b.k1 and a.k2 = b.k2
and a.dest<>b.dest

left outer join testtable c
on b.k1=c.k1 and b.k2=c.k2
and a.dest<>c.dest and b.dest<>c.dest

order by k1,k2

which results in

k1 k2 dest dest dest
a a 1 2 0
a a 2 1 0
a b 3 0 0
b c 4 5 6
b c 4 6 5
b c 5 4 6
b c 5 6 4
b c 6 4 5
b c 6 5 4

This clearly gets cumbersome as the number of possible combinations
increases.

Am I tilting at windmills, or just missing the boat?