1

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?

Improve this question
2
  • 1
    Do you really want to create a table or you're just talking about a result-set? Commented Sep 6, 2013 at 8:20
  • I want to produce a "View" that is read from a Java application Commented Sep 9, 2013 at 1:57

3 Answers 3

Reset to default
1

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

Comments

0

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.

Improve this answer

5 Comments

it's still not dynamic, he has to list all the items explicitly in for item_id in clause
@beherenow .. "If the item_id list is complete item_id list use a select query in place of item list." This would make it dynamic.
@Mani, if you think it can be done dynamically, please update your example to show how. Or perhaps did you mean to use the PIVOT XML option which allows a query for the Item List?
@Mani subquery would work with pivot xml only, conventional pivoting supports only static listing
@JeffreyKemp My bad:( apologies.. I posted the result before testing it. As you said subquery will work only for pivot xml. i didnt read the complete pivot document. It gave me chance today to read it completely. Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.