REM
/* * 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