Home » SQL & PL/SQL » SQL & PL/SQL » Complex pivot query - help (Oracle 10.2.0.4)
Complex pivot query - help [message #445259] Fri, 26 February 2010 14:19 Go to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Hi, really need help with a complex pivot query. Hoping that someone could assist. I have data in a table as follows:

ID           Fruit Numb         Fruit String
=============================================
123           20              20,21,30,60
123           21              20,21,30,60
123           30              20,21,30,60
123           60              20,21,30,60
345           80              80,22,20
345           22              80,22,20
345           20              80,22,20
1212          4               4,20,22
1212          20              4,20,22
1212          22              4,20,22
3434          20              20,21
3434          21              20,21

I need to figure out if someone likes Fruit 20 what other fruits will they are going to like.

Fruit count=total number of users that linked to this fruit
Then you see I need to pivot fruit numbers into column names adn the value of # of users that like fruit 20 and these other fruits..So my report will look like this:

Fruit   Fruit  Fruit_20 Fruit_21 Fruit_22 Fruit_30 Fruit_4
Numb    Count  
============================================================
20       7                2
21       4       2      
22       2       1
4        1       1
30       3       1        1
80       2       1
60       4       1        1    ETC


Please help!

Re: Complex pivot query - help [message #445261 is a reply to message #445259] Fri, 26 February 2010 14:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

I don't understand the result of your first line for instance with the data you posted, please explain.

Regards
Michel
Re: Complex pivot query - help [message #445266 is a reply to message #445259] Fri, 26 February 2010 22:41 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
how you compute your result?
write alorithm of calculation
Re: Complex pivot query - help [message #445277 is a reply to message #445259] Sat, 27 February 2010 10:42 Go to previous message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
> I have data in a table as follows:
Poor design that does not conform to Third Normal Form which complicates standard processing.

NEVER have any column that contains more than a single value!
Previous Topic: Result If A Subquery Returns No Data
Next Topic: what is called table function?
Goto Forum:
  


Current Time: Fri Dec 09 11:52:40 CST 2016

Total time taken to generate the page: 0.15323 seconds