Combining rows to create two columns of data

Question

I'm a bit confused on how to do this query properly. I have a table that looks like this. Where district 0 represent a value that should be applied to all district (global).

[ district ] [ code ] [ value ]
     1          A         11
     1          C         12
     2          A         13
     2          B         14
     0          B         15

I have built a query (below) to combine the "global value" on each district.

[ district ] [ code ] [ district value ] [ global value ]
      1         A            11               null        -> row 1
      1         B           null               15         -> row 2
      1         C            12               null        -> row 3
      2         A            13               null        -> row 4
      2         B            14                15         -> row 5
      2         C           null              null        -> row 6 (optional)

I did it by joining on the list of all possible district/code.

select all_code.district, all_code.code, table_d.value, table_g.value
  from (select distinct b.district, a.code
          from temp_table a
         inner join (select distinct district
                      from temp_table
                     where district <> 0) b
            on 1 = 1) all_code
  left join temp_table table_d
    on table_d.code = all_code.code
   and table_d.district = all_code.district
  left join temp_table table_g
    on table_g.code = all_code.code
   and table_g.district = 0

This query works great but seems pretty ugly. Is there a better way of doing this? (note that I don't care if row #6 is there or not).

Here's a script if needed.

create table temp_table
(
  district VARCHAR2(5) not null,
  code     VARCHAR2(5) not null,
  value    VARCHAR2(5) not null
);


insert into temp_table (district, code, value)
values ('1', 'A', '11');
insert into temp_table (district, code, value)
values ('1', 'C', '12');
insert into temp_table (district, code, value)
values ('2', 'A', '13');
insert into temp_table (district, code, value)
values ('2', 'B', '14');
insert into temp_table (district, code, value)
values ('0', 'B', '15');

Answer 1

I would argue that a lot of the "ugliness" comes from a lack of lookup tables for district and code. Without an authoritative source for those, you have to fabricate one from the values that are in use (hence the sub-queries with distinct).

In terms of cleaning up the query you have, the best I can come up with is to remove an unnecessary sub-query and use the proper syntax for the cross join:

SELECT   a.district,
         b.code,
         c.value1,
         d.value1
FROM     (SELECT DISTINCT district FROM temp_table WHERE district <> 0) a
         CROSS JOIN (SELECT DISTINCT code FROM temp_table) b
         LEFT JOIN temp_table c
            ON b.code = c.code AND a.district = c.district
         LEFT JOIN temp_table d
            ON b.code = d.code AND d.district = 0
ORDER BY district, code

Answer 2

Here is one of the options. Since you are on 10g you can make use of partition outer join(partition by() clause) to fill the gaps:

with DCodes(code) as(
  select 'A' from dual union all
  select 'B' from dual union all
  select 'C' from dual
),
DGlobal(code, value1) as(
  select code
       , value
    from temp_table
   where district = 0
)
select tt.district
     , dc.code
     , tt.value
     , dg.value1 as global_value
  from temp_table tt
       partition by(tt.district)
       right join DCodes dc 
               on (dc.code = tt.code)
       left join DGlobal dg
              on (dg.code = dc.code)
 where tt.district != 0
 order by 1, 2

Result:

DISTRICT  CODE  VALUE  GLOBAL_VALUE
--------  ----  -----  ------------
1         A     11                 
1         B            15           
1         C     12                 
2         A     13                 
2         B     14     15           
2         C