title: Solution to problem done in PL/SQL what would it look like in SQL?
body: <p>I've written a solution to problem using PL/SQL and SQL and I can't help thinking that it could be done 100% in SQL but am I am struggling to get started.</p>
  
  <p>Here is the structure of the two tables (If it helps, the scripts to create them are at the end of the question)</p>
  
  <p>Table t1 (primary key is both columns displayed)</p>
  
  <pre><code>ID    TYPE
  1     A
  1     B
  1     C
  
  2     A
  2     B
  
  3     B
  </code></pre>
  
  <p>The Type column is a Foreign Key to table T2 which contains the following data:</p>
  
  <p>Table t2 (primary key is Type)</p>
  
  <pre><code>Type    Desc
  A       xx
  
  B       xx
  
  C       xx
  </code></pre>
  
  <p>So given the the data in T1 the result I need will be:</p>
  
  <p>For ID 1 because it has all the types in the foreign key table I would return the literal "All"</p>
  
  <p>For ID 2 because it has two types I would like to return "A &amp; B" (note the separator)</p>
  
  <p>And finally for ID 3 because it has one type I would like to return just "B" </p>
  
  <p>As promised here are the scripts to create all the objects mentioned. </p>
  
  <pre><code>create table t2(type varchar2(1),
                  description varchar2(100)
                  )                
  /
  
  insert into t2
  values ('A', 'xx')
  /
  
  insert into t2
  values ('B', 'xx')
  /
  
  insert into t2
  values ('C', 'xx')
  /
  
  alter table t2 add constraint t2_pk primary key (type)
  /
  
  create table t1 (id number(10),
                   type varchar2(1)
                   )
  /
  
  alter table t1 add constraint t1_pk primary key(id, type)
  /
  
  alter table t1 add constraint t1_fk foreign key (type) 
  references t2(type)
  /
  
  insert into t1
  values (1, 'A') 
  /
  
  insert into t1
  values (1, 'B')
  /
  
  insert into t1
  values (1, 'C')
  /
  
  insert into t1
  values (2, 'A')
  /
  
  insert into t1
  values (2, 'B')
  /
  
  insert into t1
  values (3, 'B')
  /
  </code></pre>