Oracle 10g - flatten relational data dynamically

Question

Hi. 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

Answer 1

I'm sure there is a better way to do this but this is what I've come up with. Also, the question you have posted seems a little vague so I'm not entirely sure that I'm answering the question correctly.

Firstly you need to get a sparse table of all possible outcomes, then join again to get the values.

This has the output as a single column of data. It isn't really possible to have a custom number of columns per query without creating a query using dynamic SQL or something.

sys_connect_by_path is used to join the concatenate the multiple rows of data into a single row.

with table_a as (
  select 1 as id, 'nm1' as nm, 1 as enabled from dual union all
  select 2 as id, 'nm2' as nm, 1 as enabled from dual union all
  select 3 as id, 'nm3' as nm, 0 as enabled from dual union all
  select 4 as id, 'nm4' as nm, 1 as enabled from dual
),
table_b as (
  select 1 as id, 1 as a_id, 1 as docid, 0.8 as value from dual union all
  select 2 as id, 4 as a_id, 1 as docid, 0.6 as value from dual union all
  select 3 as id, 1 as a_id, 2 as docid, 0.3 as value from dual union all
  select 4 as id, 2 as a_id, 2 as docid, 0.4 as value from dual union all
  select 5 as id, 4 as a_id, 2 as docid, 0.7 as value from dual union all
  select 6 as id, 2 as a_id, 3 as docid, 0.6 as value from dual union all
  select 7 as id, 4 as a_id, 3 as docid, 0.8 as value from dual 
),
cartesian_prod as (
  select b.docid, a.id, a.nm
  from 
    table_a a
    cross join (select distinct docid from table_b) b
  where a.enabled = 1
)
select 
  docid, 
  ltrim(max(sys_connect_by_path(nm || ' ' || value, ', ')), ', ') as value
from (
  select 
    c.docid, 
    c.nm, 
    nvl(to_char(b.value), 'null') as value, 
    row_number() over (partition by c.docid order by c.id) as rown
  from 
    cartesian_prod c 
    left outer join table_b b on (b.docid = c.docid and c.id = b.a_id)
)
start with rown = 1
connect by docid = prior docid and rown = prior rown + 1
group by docid

Answer 2

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.