Oracle SQL Query Denormalisation

Question

I am trying to prepare data for a new table which is the same data from exisiting tables but denormalized. I have a simple scenario but my mind is drawing a blank at the most efficient way of returning the results

It is based on the following simplified scenario:

Table X   | Table y
id        | id    Identifier  Value
123       | 123   1           A
          | 123   2           B

Along with further fields from table X I need my query to return:

123 A B

I have considered:

Solution One

select 
id,
(select Value...),
(select Value...)...

Solution Two:

select id,
y1.Value,
y2.Value
from x, y y1, y y2...

Solution Three: Using PL/SQL and iterating through a cursor

Solution Four: Extracting y into two tables identifyer1 and identifier2 (potentially using triggers) and joining those tables within in the query instead

Each of these solutions has a major drawback for one reason or another and I'm sure one word could remind me of a concept to solution this

Judging by your output example you have all info in table Y. Or you may join X and Y by id and use Listagg() or WM_Concat (10g) to concatenate values from table Y, not pivot. No PL/SQL needed. — Art, Feb 28 '13 at 21:49

Taryn · Answer 1 · 2013-02-28T21:03:50.200

Unless I am missing something, you are trying to pivot the data. There are a couple ways to do this.

You can use an aggregate function and a CASE expression:

select x.id,
  max(case when y.identifier = 1 then y.value end) Value1,
  max(case when y.identifier = 2 then y.value end) Value2
from tablex x
left join tabley y
  on x.id = y.id
group by x.id

See SQL Fiddle with Demo

Depending on your version of Oracle you can use the PIVOT function:

select id,
  Value1,
  Value2
from
(
  select x.id, y.identifier, y.value
  from tablex x
  left join tabley y
    on x.id = y.id
) 
pivot
(
  max(value)
  for identifier in ('1' as Value1, '2' as Value2)
) piv

See SQL Fiddle with Demo

You can join multiple times:

select x.id,
  y1.value Value1,
  y2.value Value2
from tablex x
left join tabley y1
  on x.id = y1.id
  and y1.identifier = 1
left join tabley y2
  on x.id = y2.id
  and y2.identifier = 2

See SQL Fiddle with Demo.

If you are looking for a dynamic solution, then you can create a procedure using a sys_refcursor:

CREATE OR REPLACE procedure dynamic_pivot(p_cursor in out sys_refcursor)
as
    sql_query varchar2(8000) := 'select x.id ';

    begin
        for x in (select distinct identifier from tabley order by 1)
        loop
            sql_query := sql_query ||
                ' , max(case when y.identifier = '||x.identifier||' then y.value else null end) as Value'||x.identifier;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' from tablex x
                                    left join tabley y
                                      on x.id = y.id
                                    group by x.id';
        dbms_output.put_line(sql_query);

        open p_cursor for sql_query;
    end;
/

These solutions give you the results in separate columns for each value. If you want the data in a single column then you can use LISTAGG():

select x.id,
  listagg(y.value, ' ') within group (order by y.id) as Value
from tablex x
left join tabley y
  on x.id = y.id
group by x.id

See SQL Fiddle with Demo

I think he wants an arbitrary size pivot (which is easy in SQL Server with the as XML trick). — Hogan, Feb 28 '13 at 20:47
I haven't spent much time with pivot so will do some reading around that area Your first response had reminded me of the decode function so I will investigate that along with your other suggestions and do some performance analysis select x.id, max(decode(y.identifier, 1, value )), max(decode(y.identifier, 2, value )) from tablex x, tabley y where x.id = y.id group by x.id; Thanks for the quick responses. — user2121172, Feb 28 '13 at 21:34
@user2121172 the first version can be used with `decode` I always use `case` because it is not database specific. — Taryn, Feb 28 '13 at 22:15

Oracle SQL Query Denormalisation

1 Answers1