Enumerating combinations via SQL

Question

I have a table like this:

ID | name
=================================
 1 | apple
 1 | bear
 2 | cactus
 2 | dog
 2 | apple
 3 | apple
 3 | dog
==================================

and I wish to get the following unique set of combinations per ID as output from an SQL query:

===================
apple  | bear
apple  | cactus
apple  | dog
cactus | dog
===================

It's essentially a type of transpose. Ideally, I'd like to expand this to include a count of co-occurances, like this:

===================
1 | apple  | bear
1 | apple  | cactus
2 | apple  | dog
1 | cactus | dog
===================

but this may be above and beyond. I know I can accomplish all of the above using a stored proc; was just curious if I can do this as a query.

Any SQL will do, but MySQL would be preferred if possible.

Thanks for any and all input!

Answer 1

From your example, it looks like you want the first column to contain whatever name comes first alphabetically, so I used a < when joining the table to itself.

select count(*), t1.Name, t2.Name
from 
    tblThings t1 join
    tblThings t2 ON t1.ID = t2.ID AND t1.Name < t2.Name
group by t1.Name, t2.Name

And here's the query in action to prove I'm not crazy.

Answer 2

Your first step is to get a list of combinations.

SELECT * FROM names n1 
INNER JOIN names n2 ON n1.id = n2.id 
WHERE n1.name < n2.name;

This will yield

1|apple|1|bear
2|cactus|2|dog
2|apple|2|cactus
2|apple|2|dog
3|apple|3|dog

Your next step is to COUNT unique combinations of name1, name2.

SELECT COUNT(1), name1, name2
FROM (
  SELECT n1.name AS name1, n2.name AS name2
  FROM names n1 INNER JOIN names n2 ON n1.id = n2.id
  WHERE n1.name < n2.name) x 
GROUP BY name1, name2

Which yields

1|apple|bear
1|apple|cactus
2|apple|dog
1|cactus|dog