Pivot a result set

You Asked

Hi Tom,
I have two tables which are a 1 - M relationship.
I have a user who want a horizontal view of the
data.

For instance,
Table A
Key-1 char(3);
table A values
_______
1
2
3


Table B
bkey-a char(3);
bcode  char(1);
table b values
1 T
1 A
1 G
2 A
2 T
2 L
3 A

What he wants to see is
all matches between the tables
where the code
is "T" "A" or "L":
1, T, A
2, A, T, L
3, A

I have tried joining the tables to themselves 
and doing an outer join but I end up
with multiple rows.
1, T
1, A
2  a
2  t
2  l
3  a 
etc

Is this possible?

 

and we said...

Sure, using decode to transpose columns like this is fairly straightforward.  I'll do it 
in 2 steps so you can see how it is done.

We start by joining A to B and creating a 'sparse' matrix.  We'll then "squash out" the 
redundant rows giving us the desired effect.

ops$tkyte@8i> select key_1,
  2             decode( bcode, 'T', bcode, null ) t_code,
  3             decode( bcode, 'A', bcode, null ) a_code,
  4             decode( bcode, 'L', bcode, null ) l_code
  5    from a, b
  6   where a.key_1 = b.bkey_a
  7  /

KEY T A L
--- - - -
1   T
1     A
1
2     A
2   T
2       L
3     A

7 rows selected.


So, there is our 'sparse' matrix.  What we want to do now is collapse the rows by key_1.  
Thats what group by does for us:


ops$tkyte@8i> 
ops$tkyte@8i> select key_1,
  2             max(decode( bcode, 'T', bcode, null )) t_code,
  3             max(decode( bcode, 'A', bcode, null )) a_code,
  4             max(decode( bcode, 'L', bcode, null )) l_code
  5    from a, b
  6   where a.key_1 = b.bkey_a
  7   group by key_1
  8  /

KEY T A L
--- - - -
1   T A
2   T A L
3     A


You could add a where clause in the event you have a row in A, such there no rows in B 
have the value T, A, L.  EG:

ops$tkyte@8i> insert into a values ( '4' );
ops$tkyte@8i> insert into b values ( '4', 'Z' );

ops$tkyte@8i> select key_1,
  2             max(decode( bcode, 'T', bcode, null )) t_code,
  3             max(decode( bcode, 'A', bcode, null )) a_code,
  4             max(decode( bcode, 'L', bcode, null )) l_code
  5    from a, b
  6   where a.key_1 = b.bkey_a
  7   group by key_1
  8  /

KEY T A L
--- - - -
1   T A
2   T A L
3     A
4                  <<<<<====== you might not want that row (maybe you do?)

ops$tkyte@8i> select key_1,
  2             max(decode( bcode, 'T', bcode, null )) t_code,
  3             max(decode( bcode, 'A', bcode, null )) a_code,
  4             max(decode( bcode, 'L', bcode, null )) l_code
  5    from a, b
  6   where a.key_1 = b.bkey_a
  7     and b.bcode in ( 'T', 'A', 'L' )    <<<<<====== that'll get rid of it.
  8   group by key_1
  9  /

KEY T A L
--- - - -
1   T A
2   T A L
3     A