Pivot in Oracle SQL

Recently i came across one post on OTN in which user need pivot kind of output and people giving him some weird and lengthy solution so i thought to share this on my blog:

first of all we need to prepare test table and test data

CREATE TABLE fruits
(
person VARCHAR2(30),
fruitname VARCHAR2(30)
);


insert into fruits values ('abhi','APPLE');
insert into fruits values ('abhi','LICHI');
insert into fruits values ('abhi','LICHI');
insert into fruits values ('abhi','LICHI');
insert into fruits values ('abhi','LICHI');
insert into fruits values ('abhi','LICHI');
insert into fruits values ('abhi','LICHI');
insert into fruits values ('abhi','LICHI');
insert into fruits values ('abhi','MANGO');
insert into fruits values ('abhi','MANGO');
insert into fruits values ('abhi','MANGO');
insert into fruits values ('abhi','MANGO');
insert into fruits values ('abhi','MANGO');
insert into fruits values ('abhi','ORANGE');
insert into fruits values ('abu','APPLE');
insert into fruits values ('abu','APPLE');
insert into fruits values ('abu','APPLE');
insert into fruits values ('abu','LICHI');
insert into fruits values ('abu','LICHI');
insert into fruits values ('abu','LICHI');
insert into fruits values ('abu','LICHI');
insert into fruits values ('abu','LICHI');
insert into fruits values ('abu','LICHI');
insert into fruits values ('abu','LICHI');
insert into fruits values ('abu','LICHI');
insert into fruits values ('abu','MANGO');
insert into fruits values ('abu','MANGO');
insert into fruits values ('abu','ORANGE');
insert into fruits values ('anirudh','APPLE');
insert into fruits values ('anirudh','APPLE');
insert into fruits values ('anirudh','APPLE');
insert into fruits values ('anirudh','APPLE');
insert into fruits values ('anirudh','APPLE');
insert into fruits values ('anirudh','APPLE');
insert into fruits values ('anirudh','LICHI');
insert into fruits values ('anirudh','LICHI');
insert into fruits values ('anirudh','LICHI');
insert into fruits values ('anirudh','LICHI');
insert into fruits values ('anirudh','LICHI');
insert into fruits values ('anirudh','MANGO');
insert into fruits values ('anirudh','MANGO');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('anirudh','ORANGE');
insert into fruits values ('shakti','APPLE');
insert into fruits values ('shakti','APPLE');
insert into fruits values ('shakti','APPLE');
insert into fruits values ('shakti','APPLE');
insert into fruits values ('shakti','LICHI');
insert into fruits values ('shakti','MANGO');
insert into fruits values ('shakti','MANGO');
insert into fruits values ('shakti','MANGO');
insert into fruits values ('shakti','MANGO');
insert into fruits values ('shakti','MANGO');
insert into fruits values ('shakti','MANGO');
insert into fruits values ('shakti','ORANGE');
insert into fruits values ('shakti','ORANGE');
insert into fruits values ('shakti','ORANGE');
insert into fruits values ('shakti','ORANGE');
insert into fruits values ('shakti','ORANGE');

Solution 1: Using aggregate function and case statement:

SQL> SELECT person,
  2  SUM(CASE WHEN fruitname='APPLE' THEN 1 ELSE 0 END) AS APPLE,
  3  SUM(CASE WHEN fruitname='LICHI' THEN 1 ELSE 0 END) AS LICHI,
  4  SUM(CASE WHEN fruitname='MANGO' THEN 1 ELSE 0 END) AS MANGO,
  5  SUM(CASE WHEN fruitname='ORANGE' THEN 1 ELSE 0 END) AS ORANGE
  6  FROM fruits
  7  GROUP BY person
  8  ;

PERSON                              APPLE      LICHI      MANGO     ORANGE
------------------------------ ---------- ---------- ---------- ----------
abhi                                    1          7          5          1
abu                                     3          8          2          1
anirudh                                 6          5          2          9
shakti                                  4          1          6          5

Solution 2: Using PIVOT Operator:

SQL> SELECT * FROM    (SELECT PERSON ,FRUITNAME
  2          FROM   fruits)
  3  PIVOT  (COUNT(FRUITNAME) AS FRUITNAMES FOR (FRUITNAME) IN ('APPLE' AS apple, 'MANGO' AS mango, 'ORANGE' AS orange));

PERSON                         APPLE_FRUITNAMES MANGO_FRUITNAMES ORANGE_FRUITNAMES
------------------------------ ---------------- ---------------- -----------------
abhi                                          1                5                 1
abu                                           3                2                 1
anirudh                                       6                2                 9
shakti                                        4                6                 5

Note: Here i have not included LITCHI