Home » SQL & PL/SQL » SQL & PL/SQL » Pivoting table with 3 dimentions (oracle DB version 9.2.0.1 win xp professional sp2)
Pivoting table with 3 dimentions [message #339893] Sat, 09 August 2008 08:55 Go to next message
ohadw
Messages: 2
Registered: August 2008
Junior Member
Hi all

I have a compicate problem.

I have a table that contains data that I want to show in a 3 dimension table.

The base table:
   	ACTIVITY_ID	PRODUCT_ID	QUANTITY PRICE
1	P-100	2-102	-0.5000	-5.0000
2	P-100	2-107	-500.0000	-12.0000
3	P-100	P-201	-0.6000	-5.6100
4	P-100	P-100	2.0000	282.6100
5	P-101	2-102	-2.5000	-25.0000
6	P-101	2-103	-1.0000	-4.0000
7	P-101	2-108	-0.5000	-8.0000
8	P-101	3-100	-5.0000	-100.0000
9	P-101	P-201	-1.5000	-14.0250
10	P-101	P-101	5.0000	281.0250
11	P-102	2-100	-4.0000	-12.0000
12	P-102	P-102	8.0000	272.0000
13	P-201	2-100	-2.1000	-6.3000


I want to present the data in a ref cursor or something like this in the format of pivot table. The x axis is the activities, the y axis is the products, and the inner is the quantity, and the price
example of the result:
PRODUCT_ID ACTIVITY_ID_1,   ACTIVITY_ID_2, ...ACTIVITY_ID_n
           QUANTITY PRICE   QUANTITY PRICE    QUANTITY PRICE
2-102      -0.5000  -5.0000 -2.5000  -25.0000
2-103                       -1.0000  -4.0000


Additional questions and information:
1. The number of columns can vary and determined in runtime.
2. Is ther a possibilty to give the column headers names of the activities? (means that instead of ACTIVITY_ID_1, ACTIVITY_ID_2 I'll see P-100, P-101 and so on)?
3.I've tried to use the pivoting option that i've read about in the web, but its not giving the desired result.

I hope that someone can help me with that soon, or at least give me a clue to solve it.

Thank

Ohad



[mod-edit: code tags added by bb, next time pleas add them yourself]

[Updated on: Sat, 09 August 2008 09:30] by Moderator

Report message to a moderator

Re: Pivoting table with 3 dimentions [message #339895 is a reply to message #339893] Sat, 09 August 2008 08:58 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW Posting Guidelines as stated in URL above.
Re: Pivoting table with 3 dimentions [message #339906 is a reply to message #339893] Sat, 09 August 2008 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel

Re: Pivoting table with 3 dimentions [message #339908 is a reply to message #339906] Sat, 09 August 2008 11:10 Go to previous messageGo to next message
ohadw
Messages: 2
Registered: August 2008
Junior Member
I hope that this is what you've ment.
Anyway, I reduced the data in the pivot into one type (lets say quantity), and the table shows the quantity needed for each activity_id (columns P-100, p-101, P-102) and product_id

create table sim_results_pivot
(
  P_100      varchar2(10),
  P_101      varchar2(10),
  P_103      varchar2(10),
  product_id varchar2(10)
)


insert into SIM_RESULTS_PIVOT (P_100, P_101, P_103, PRODUCT_ID)
values ('2', '4', null, '2-100');
insert into SIM_RESULTS_PIVOT (P_100, P_101, P_103, PRODUCT_ID)
values (null, '1', '5', '2-101');
insert into SIM_RESULTS_PIVOT (P_100, P_101, P_103, PRODUCT_ID)
values ('1', null, null, '2-202');
commit;


Thanks

Ohad
Re: Pivoting table with 3 dimentions [message #339910 is a reply to message #339908] Sat, 09 August 2008 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the relation between what you posted and the base table from your first post?
What is the relation between what you posted and the result in you first post?
What should be the result from what you posted?

Anyway, I already answered your questions in other topics, search for "pivot".

Regards
Michel
Re: Pivoting table with 3 dimentions [message #339929 is a reply to message #339908] Sat, 09 August 2008 17:09 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
ohadw wrote on Sat, 09 August 2008 09:10

I hope that this is what you've ment.



No, that is not what we are asking for. What we are asking for is the statement to create the base table with the activity_id, product_id, quantity, and price columns and insert statements to insert the data corresponding to what you listed in your original post for the original data. This is all explained in the forum guidelines. Had you provided that information in your original post, you might have had a complete demonstration hours ago. In general, you need to wrap an aggregate function such as max or min or sum around decode to get the proper pivoted columns. Since you don't know how many columns there should be in the result set or their names, you will need to build your sql select statement dynamically.


Previous Topic: raising an exception
Next Topic: How to send a mail from PL/sql
Goto Forum:
  


Current Time: Sat Dec 03 20:35:15 CST 2016

Total time taken to generate the page: 0.04224 seconds