Re: Produce set of sets using SQL

["Carel Teijgeler" <coteijgeler@xxxxxxxxx>]

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?