REM <PRE>
/*
 * PivotExamples.Sql (c) Tim Onions 2008
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */

COLUMN PivotList FORMAT A80 NEW_VALUE PivotList
PROMPT
PROMPT Example #1 - Pivot employee name into a list for each department (acknowledgements to askTom)
WITH basedata AS
(SELECT --+ Place the query you wish to pivot here, amending the ROW_NUMBER clause as necessary
        deptno AS GroupCol
       ,ename  AS PivotCol
       ,ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY ename) rown
   FROM emp
)
SELECT GroupCol
      ,LTRIM(MAX(SYS_CONNECT_BY_PATH(PivotCol,',')),',') AS PivotList
  FROM basedata
START WITH rown=1
CONNECT BY PRIOR rown=rown-1
       AND PRIOR GroupCol=GroupCol
GROUP BY GroupCol
ORDER BY GroupCol;

PROMPT Example #2 - Pivot employee name into a list for department 10 (acknowledgements to LazyDBA)
WITH basedata AS (SELECT --+ Place the query you wish to pivot here
                         ename AS PivotCol
                    FROM emp
                   WHERE deptno=10)
    ,datatab  AS (SELECT PivotCol
                        ,ROW_NUMBER() OVER (ORDER BY PivotCol) rown
                        ,COUNT(*) OVER () cnt
                    FROM basedata)
SELECT LTRIM(SYS_CONNECT_BY_PATH(PivotCol,','),',') PivotList
  FROM datatab
 WHERE rown=cnt
START WITH rown=1
CONNECT BY PRIOR rown=rown-1;

PROMPT Example #3 - Un-pivot employee name comma separated list from last example back into one row per item
PROMPT [NB: list used is: "&&PivotList"]
SET ECHO off VER off
COLUMN DataItem FORMAT A40 HEADING "Unpivot Item"
WITH datatab AS
(SELECT TRIM(SUBSTR(data,
         INSTR(data,',',1,LEVEL)+1,
         INSTR(data,',',1,LEVEL+1) 
            - INSTR(data,',',1,LEVEL)-1)) DataItem
   FROM (SELECT ','||q'[&&PivotList]'||',' data
           FROM dual)
 CONNECT BY LEVEL<=LENGTH(q'[&&PivotList]')-LENGTH(REPLACE(q'[&&PivotList]',',',''))+1
 )
SELECT DataItem
  FROM datatab;

/* Pivoting column lists - there are a number of these clever usages of SQL to display the results of a query 
   as a list of values rather than a set of rows, here are a couple of my favourites (inspired by askTom and LazyDBA). 
   Plus,  SQL is given to take a list an "un-pivot" it into one entry per row. 

   Sample code might look like:

Example #1 - Pivot employee name into a list for each department (acknowledgements to askTom)

 GROUPCOL PIVOTLIST
--------- --------------------------------------------------------------------------------
       10 CLARK,KING,MILLER
       20 ADAMS,FORD,JONES,SCOTT,SMITH
       30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Example #2 - Pivot employee name into a list for department 10 (acknowledgements to LazyDBA)

PIVOTLIST
--------------------------------------------------------------------------------
CLARK,KING,MILLER

Example #3 - Un-pivot employee name comma separated list from last example back into one row per item
[NB: list used is: "CLARK,KING,MILLER"]

Unpivot Item
----------------------------------------
CLARK
KING
MILLER
*/

REM </PRE>