I am using Oracle 10g. and I have the following relational structure that I think I need to flatten out so on the client side a grid view shows the correct number of columns.
TableA below can be configured to have any one of it's records enabled or disabled using the enabled field.
TableB stores calculated values related to TableA via the field fk. For docid 1 there are values calulated for 'nm1' and 'nmn4' but not for 'nm2'.
My problem is that for a particular configuration of TableA I need to return a record set having the full complement of enabled records in TableA whether or not a docid in TableB has a calculated value for it. The ouput I am looking to dynamically create is show below.
Any ideas?
TableA
id nm enabled
1 'nm1' 1
2 'nm2' 1
3 'nm3' 0
4 'nm4' 1
TableB
id fk(A.id) docid value
1 1 1 .8
2 4 1 .6
3 1 2 .3
4 2 2 .4
5 4 2 .7
6 2 3 .6
7 4 3 .8
Output as records
1 'nm1' .8 'nm2' null 'nm4' .6
2 'nm1' .3 'nm2' .4 'nm4' .7
3 'nm1' null 'nm2' .6 'nm4' .8
This looks like a subspecies of pivot query to me. You can do the filtering by joining table B against table A, then restricting on enabled (something like select B.* from B, A where B.A_id = A.id and A.enabled = 1). You can then pivot that.