Oracle 11g - Pivot and UnPivot - Split rows into Multiple colums or vice versa

Transposing rows into columns (pivot) or columns into rows (unpivot) to generate results in crosstab format. Pivoting is a very useful technique, especially for reporting.



 I/P:

--------- Table1



Location    Month      Balances

------------------ -----------

A1               1                 100

A1               2                 200

A1               3                 300

A1               4                 400

A1               5                 500

A1               6                 600

A1               7                 800


 Output

----------------



Location         1           2           3          4        5        6        7

----------     ---        ----         ---       ---        ---     ----    -----
 A1             100       200         300      400     500      600     800



Sample Sql :
------------

 SELECT location,month,balances from table 1
  PIVOT (balances           -- pivot_clause
         FOR month         -- pivot_for_clause
         IN  (1,2,3,4,5,6,7)  -- pivot_in_clause
        );



 UNPIVOT:-
 --------

 Try it: same as pivot (instead of Pivot use Unpivot)

Oracle 11g-Listagg - Merge Rows into Single Columns

The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings.

Sample Sql: 

select listagg(''''||NAME||'''',',') within group (order by NAME) from PS_PERSON 

I/P

---

Name

------

A

B

C

D

E

The Output as

-----------------

'A','B','C','D','E'


:-)