Infer overall group from sub groups using SQL

I've got a number of sub-groups that I'm trying to use to infer the overall groups. Think of it as trying to infer the requirements for a major by the classes that individuals take, with the conditions:

people in different majors cannot take the same class
nobody has completed all classes, but each class has been taken by at least one person

So starting here:

class    activity   individual
 1        fishin        A
 1        fishin        B
 2        trappin       A
 2        trappin       C
 3        hikin         B
 3        hikin         C
 4        jumpin        D
 4        jumpin        E
 5        rollin        E

Would become:

class    activity   individual    classes in major
 1        fishin        A          fishin, hikin, trappin
 1        fishin        B          fishin, hikin, trappin
 2        trappin       A          fishin, hikin, trappin
 2        trappin       C          fishin, hikin, trappin
 3        hikin         B          fishin, hikin, trappin
 3        hikin         C          fishin, hikin, trappin
 4        jumpin        D          jumpin, rollin
 4        jumpin        E          jumpin, rollin
 5        rollin        E          jumpin, rollin

I imagine this would entail group_concat, but am trying to figure out the overall query.

Solution

You'll need at least sqlite 3.8.3 to use a recursive CTE to do this:

with recursive x as (
  select
    individual,
    activity
  from
    tbl
  union
  select
    x.individual,
    t2.activity
  from
    x
        inner join
    tbl t1
        on x.activity = t1.activity
        inner join
    tbl t2
        on t1.individual = t2.individual
), y as (
  select
    individual,
    group_concat(distinct activity) g
  from
    x
  group by
    individual    
) select
  t.*,
  y.g
from
  y
    inner join
  tbl t
    on y.individual = t.individual;

I couldn't figure out a way to influence the order of the items in the groups. Also, SQLFiddle doesn't have a high enough version of SQLite to show a demo.