theshining theshining - 1 month ago 6
SQL Question

Oracle Database - Dynamic number of columns

I have the following 3 Oracle Database Tables:

application (app_id, application_name)


eg. (1, firstapp)

item_request (app_id, item_id, qty_requested)


eg. (1, 111, 5), (1, 112, 3), (1, 113, 7)

item (item_id, item_code)


eg. (111, "COMPUTER"), (112, "PHONE"), (113, "DESK")

I want to produce this table:

new table (app_id,
application_name,
qty_requested_for_item_with_item_code_111,
qty_requested_for_item_with_item_code_112,
qty_requested_for_item_with_item_code_113,
etc...)


eg. (1, fistapp, 5, 3, 7, etc...)

Is this even possible?

Answer

You can achieve this using dynamic SQL, e.g. with a ref cursor. You have to

a)iterate over all existing items

b)build your SELECT list by adding all items

c)perform the pivoting (either by using PIVOT or with the traditional MAX / CASE / GROUP BY) approach

create table application(app_id number primary key, application_name varchar2(30));
create table item_request(app_id number, item_id number, qty_requested number);
create table item(item_id number primary key, item_code varchar2(30));

insert into application values(1, 'firstapp');
insert into application values(2, 'secondapp');
insert into item values (111, 'Computer');
insert into item values (112, 'Phone');
insert into item values (113, 'Desk');
insert into item_request values (1, 111, 5);
insert into item_request values (1, 112, 3);
insert into item_request values (1, 113, 7);
insert into item_request values (2, 111, 3);

-- SQL/Plus syntax for declaring and using a bind variable of type ref cursor
var x refcursor;

set autoprint on

declare
  l_sql varchar2(4000);
  l_select varchar2(4000);
  l_from varchar2(4000);
begin
  l_select := 'select application.app_id, application.application_name';
  for cur in (select * from item)
    loop
        l_select := l_select || chr(10) || ',max(case when item_code = ''' || cur.item_code || ''' then qty_requested else 0 end) as ' || cur.item_code;
      end loop;
  l_sql := l_select || ' 
    from application 
    left join item_request on application.app_id = item_request.app_id 
    left join item on item.item_id = item_request.item_id
    group by application.app_id, application.application_name';
  dbms_output.put_line(l_sql);
  open :x for l_sql;
end;