CrossTab

Hi,

I'm glad you asked this question. I have a recipe
for a similar query in my upcoming book and
I was juggling with a few solutions.
Answering it here will give me a chance to get some
real feedback on my proposed solution :)

As always, there's actually a few ways to do this,
here's one approach:


SQL> select * from food;

WHO APPLES PEACHES ORANGES PIZZA
---- ---------- ---------- ---------- ----------
john 5 1 2 15
mary 1 3 4 12
jill 2 1 6 6



/* the key is transforming the columns into rows, you can
do that with a cartesian product then keep only
the rows you need
*/


SQL> select decode(iter.pos,
2 1,'apples',
3 2,'peaches',
4 3,'oranges',
5 4,'pizza' ) fruit,
6 decode(iter.pos,
7 1,food.apples,
8 2,food.peaches,
9 3,food.oranges,
10 4,food.pizza) cnt,
11 food.who,
12 iter.pos
13 from food,
14 (select rownum pos from all_objects) iter
15 where iter.pos <= 4
16 order by who;

FRUIT CNT WHO POS
------- ---------- ---- ----------
apples 2 jill 1
peaches 1 jill 2
oranges 6 jill 3
pizza 6 jill 4
apples 5 john 1
peaches 1 john 2
pizza 15 john 4
oranges 2 john 3
apples 1 mary 1
oranges 4 mary 3
pizza 12 mary 4
peaches 3 mary 2


/* in the query above, I named the inline view that select from
all_objects, ITER, and rownum, POS, to indicate you are
sort of "iterating" until a certain "position". In this
case, there are 4 food columns, so 4 "iterations" (rows) need
to be generated. POS is also used to provide a "ranking"
for WHO and FRUIT - you'll need this later when grouping
*/



/* the next step is to use DECODE to start organizing the data
into groups - in the book I think I use CASE cuz it's universal
- either work fine here
*/

SQL> select who,
2 fruit,
3 decode(who,'john',cnt) john,
4 decode(who,'mary',cnt) mary,
5 decode(who,'jill',cnt) jill,
6 pos
7 from (
8 select decode(iter.pos,
9 1,'apples',
10 2,'peaches',
11 3,'oranges',
12 4,'pizza' ) fruit,
13 decode(iter.pos,
14 1,food.apples,
15 2,food.peaches,
16 3,food.oranges,
17 4,food.pizza) cnt,
18 food.who,
19 iter.pos
20 from food,
21 (select rownum pos from all_objects) iter
22 where iter.pos <= 4
23 )
24 order by who;

WHO FRUIT JOHN MARY JILL POS
---- ------- ---------- ---------- ---------- ----------
jill apples 2 1
jill peaches 1 2
jill oranges 6 3
jill pizza 6 4
john apples 5 1
john peaches 1 2
john pizza 15 4
john oranges 2 3
mary apples 1 1
mary oranges 4 3
mary pizza 12 4
mary peaches 3 2



/* at this point, the last step is to bump the rows for
JOHN and MARY up, to do that you need to get rid of the
NULLS. This is done by grouping by FRUIT and POS while
taking the max for JOHN, MARY, and JILL
*/


SQL> select fruit,
2 max(decode(who,'john',cnt)) john,
3 max(decode(who,'mary',cnt)) mary,
4 max(decode(who,'jill',cnt)) jill
5 from (
6 select decode(iter.pos,
7 1,'apples',
8 2,'peaches',
9 3,'oranges',
10 4,'pizza' ) fruit,
11 decode(iter.pos,
12 1,food.apples,
13 2,food.peaches,
14 3,food.oranges,
15 4,food.pizza) cnt,
16 food.who,
17 iter.pos
18 from food,
19 (select rownum pos from all_objects) iter
20 where iter.pos <= 4
21 )
22 group by fruit,pos
23 order by decode(fruit,'oranges','yoranges','pizza','zizza',fruit);

FRUIT JOHN MARY JILL
------- ---------- ---------- ----------
apples 5 1 2
peaches 1 3 1
oranges 2 4 6
pizza 15 12 6



/* the funky ORDER BY at the end is to ensure the results are
displayed how you had them in your post - there's other
ways to do that as well
*/


Let me know if this has been helpful. If so, this will be the approach
I use for
the Oracle recipe that deals with creating cross tab results when n
columns
are involved ( I will be covering 5 dbms's, not just Oracle ).


Thanks and regards,
Anthony


-----Original Message-----
From: RM via oracle-db-l [mailto:oracle-db-l@Groups.ITtoolbox.com]
Sent: Sunday, February 13, 2005 1:02 AM
To: Anthony Molinaro
Subject: [oracle-db-l] Cross Tab


# Oracle Database 10g: A Revolution in Database Technology
# Read Paper: http://oracle.ITtoolbox.com/r/hdr.asp?r=41933

# View Group Archive: http://oracle.ITtoolbox.com/hrd.asp?i=838

Hi, I have the following table...

apples peaches oranges pizza
john 5 1 2 15
mary 1 3 4 12
jill 2 1 6 6

How is it possible to create a cross-tab (table/view) in sql or plsql
(but I prefer regular sql) so that the data now looks like this...

john mary jill
apples 5 1 2
peaches 1 3 1
oranges 2 4 6
pizza 15 12 6