Horizontal to vertical data display

- Horizontal data display is the tradational and default way to display the Oracle data.
- To change the vertical way, some examples are here.

SQL>select * from disp;
YEAR DEPT                        AMT
---- -------------------- ----------
1999 SALES                      2000
1999 PURCHASE                   4000
1999 IT                         3500
1999 SALES                      1500
1998 PURCHASE                   8000
1998 SALES                      7500

6 rows selected.


SQL>SELECT year,sum(decode(dept,'SALES',amt)) SALES,
        SUM(DECODE(dept,'PURCHASE',amt)) PURCHASE
FROM disp
GROUP BY year;
 
YEAR      SALES   PURCHASE
---- ---------- ----------
1999       3500       4000
1998       7500       8000

- The following pivot function will works only in Oracle 11i onwards

SQL> SELECT * from disp pivot (sum(amt) for dept in('SALES','PURCHASE','IT'))

YEAR    'SALES' 'PURCHASE'       'IT'
---- ---------- ---------- ----------
1999       3500       4000       3500
1998       7500       8000