CODEWITHSUNDEEP
sqloracle-databasedynamic
theshining
theshining

Reputation: 15

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?

Upvotes: 1

Views: 1747

Answers (3)

Frank Schmitt
Frank Schmitt

Reputation: 30775

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;

Upvotes: 1

Taemyr
Taemyr

Reputation: 3437

Oracle has limited support for Pivot functions.

See e.g. http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html.

In short, for regular output you are stuck with having a static list of columns that you are pivoting into.

For dynamic pivot tables you can use the pivot xml function. But this produces the pivot columns in xml, and so will need parsing.

Upvotes: 1

DB_learner
DB_learner

Reputation: 1026

You can application and item request based on app_id and do a pivot based on itemid and get quantity.

select * from (select app_id, item_id, qty from application a, item_request ir 
where a.app_id = ir.app_id) pivot(sum(qty) for item_id in (Item List)).

If the item_id list is complete item_id list use a select query in place of item list.

Upvotes: 0

Related Questions