===== SQL Pivot Funktion ===== ** ab 11g ** ** erstellt März 2015 ** Pivot => französisch: Angelpunkt Mit der SQL Pviot Funktion können Werte aus Zeilen in Spalten transponiert werden. Für gegenteilige Funktion siehe [[prog:sql_oracle_unpivot|SQL UNPivot Funktion]]. === Beispiel 1 === In diesem Bespiel erzeugen wir eine Liste der Abteilungen mit der Anzahl der Angestellten. Diese Listensicht soll in einer Zeile dargestellt werden. {{ :prog:sql_pivot_transpont_table_v01.png?300 | Tabelle soll transponiert dargestellt werden}} Beispiel: select * from ( select d.dname , COUNT (*) as percount from emp e , dept d where d.deptno = e.deptno group by d.dname ) pivot ( MAX (percount) for dname in ('ACCOUNTING' as ACCOUNTING ,'RESEARCH' as RESEARCH ,'SALES' as SALES ) ) / ACCOUNTING RESEARCH SALES ---------- ---------- ---------- 296 5 6 Es können auch mehr als eine Spalte ausgewählt werden, einfach die Aggregat Funktion auf die Spalte durch komma getrennt hintereiander schrieben und je einen alias vergeben. Der Alias wird dann zu den Aliase in der "IN" Klause hinzugefügt. Beispiel: SQL>SELECT * 2 FROM ( SELECT d.dname 3 , count (*) AS percount 4 , sum(sal) as persum 5 , loc 6 FROM emp e 7 , dept d 8 WHERE d.deptno = e.deptno 9 GROUP BY d.dname,d.loc 10 ) 11 pivot ( 12 max (percount) as percount, sum(persum) as persum 13 FOR dname 14 IN ('ACCOUNTING' AS ACCOUNTING 15 ,'RESEARCH' AS RESEARCH 16 ,'SALES' AS SALES 17 ) 18 ) 19 / LOC ACCOUNTING_PERCOUNT ACCOUNTING_PERSUM RESEARCH_PERCOUNT RESEARCH_PERSUM SALES_PERCOUNT SALES_PERSUM --------------------------------------- ------------------- ----------------- ----------------- --------------- -------------- ------------ NEW YORK Düsseldorf DALLAS 5 10875 CHICAGO 6 9400 oder auch: .. ( MAX (CURRENT_UTILIZATION) as CUR_UTL , MAX (MAX_UTILIZATION) as MAX_UTL, max(LIMIT_VALUE) As MAX_LIMIT FOR RESOURCE_NAME IN ( 'processes' AS proc ,'sessions' AS sess ) INSTANCE_NUMBER BEGIN_INTERVAL_T PROC_CUR_UTL PROC_MAX_UTL PROC_MAX_L SESS_CUR_UTL SESS_MAX_UTL SESS_MAX_L --------------- ---------------- ------------ ------------ ---------- ------------ ------------ ---------- .... === Beispiel 2 - Darstellung mit XML === Mit dem **pivot XML** kann eine sub query verwendt werden, damit wird das ganze deutlich dynamischer select * from ( select d.dname , COUNT (*) as percount from emp e , dept d where d.deptno = e.deptno group by d.dname ) pivot XML ( MAX (percount) as counter for dname in (select dname from dept where deptno > 5) ) -- Erzeugt: ACCOUNTING296 OPERATIONS RESEARCH 5 SALES 6 Das Ergebniss kann dann wieder mit **select * from XMLTABLE( .. ** ausgewertet werden. Mit dem Ausdruck **ANY** kann auch auf die Subquery verzichtet werden select * from ( select d.dname , COUNT (*) as percount from emp e , dept d where d.deptno = e.deptno group by d.dname ) pivot XML ( MAX (percount) for dname in ( ANY ) ) === Beispiel 3 - zum Filtern und transponieren === Zum Beispiel bei Filtern von Statistiken für Überwachungszwecke: select * from (select SN.NAME as statname , GS.VALUE from GV$SYSSTAT gs, gv$statname sn where GS.INST_ID = SN.INST_ID and GS.STATISTIC# = SN.STATISTIC#) pivot ( SUM (VALUE) for statname in ( 'session logical reads' as session_logical_reads , 'file io wait time' as file_io_wait_time ) ) / === Beispiel 4 - Alle Spalten eines mehrspaltigen Indexes nebeneinander anzeigen lassen === Eine Liste aller Indexes mit mehr als einer Spalte des Schemas ist zu erstellen, die Spalten des Index sollen nebeneinander bis zur 9. Stelle dargestellt werden: select * from ( select * from ( select index_owner , table_name , index_name , column_name , column_position from dba_ind_columns where index_owner like '&&USERNAME.%' order by index_owner,table_name ) pivot ( min (column_name) for column_position in ('1' as pos1 ,'2' as pos2 ,'3' as pos3 ,'4' as pos4 ,'5' as pos5 ,'6' as pos6 ,'7' as pos7 ,'8' as pos8 ,'9' as pos9 ) ) ) where pos2 is not null / ==== Beispiel 5 - Übersicht über die Log Switche ==== Ziel ist es ein Übersicht über die Log Switche der letzen Tage einer Datenbank: set linesize 130 column T01 format a3 heading "01" JUSTIFY CENTER column T02 format a3 heading "02" JUSTIFY CENTER column T03 format a3 heading "03" JUSTIFY CENTER column T04 format a3 heading "04" JUSTIFY CENTER column T05 format a3 heading "05" JUSTIFY CENTER column T06 format a3 heading "06" JUSTIFY CENTER column T07 format a3 heading "07" JUSTIFY CENTER column T08 format a3 heading "08" JUSTIFY CENTER column T09 format a3 heading "09" JUSTIFY CENTER column T10 format a3 heading "10" JUSTIFY CENTER column T11 format a3 heading "11" JUSTIFY CENTER column T12 format a3 heading "12" JUSTIFY CENTER column T13 format a3 heading "13" JUSTIFY CENTER column T14 format a3 heading "14" JUSTIFY CENTER column T15 format a3 heading "15" JUSTIFY CENTER column T16 format a3 heading "16" JUSTIFY CENTER column T17 format a3 heading "17" JUSTIFY CENTER column T18 format a3 heading "18" JUSTIFY CENTER column T19 format a3 heading "19" JUSTIFY CENTER column T20 format a3 heading "20" JUSTIFY CENTER column T21 format a3 heading "21" JUSTIFY CENTER column T22 format a3 heading "22" JUSTIFY CENTER column T23 format a3 heading "23" JUSTIFY CENTER column T24 format a3 heading "24" JUSTIFY CENTER column slday format a5 heading "Day" JUSTIFY LEFT select to_char(to_date(to_char(slday),'yyyymmdd'),'DD.MM') as slday , decode(nvl(T01,0),0,'-',to_char(T01)) T01 , decode(nvl(T02,0),0,'-',to_char(T02)) T02 , decode(nvl(T03,0),0,'-',to_char(T03)) T03 , decode(nvl(T04,0),0,'-',to_char(T04)) T04 , decode(nvl(T05,0),0,'-',to_char(T05)) T05 , decode(nvl(T06,0),0,'-',to_char(T06)) T06 , decode(nvl(T07,0),0,'-',to_char(T07)) T07 , decode(nvl(T08,0),0,'-',to_char(T08)) T08 , decode(nvl(T09,0),0,'-',to_char(T09)) T09 , decode(nvl(T10,0),0,'-',to_char(T10)) T10 , decode(nvl(T11,0),0,'-',to_char(T11)) T11 , decode(nvl(T12,0),0,'-',to_char(T12)) T12 , decode(nvl(T13,0),0,'-',to_char(T13)) T13 , decode(nvl(T14,0),0,'-',to_char(T14)) T14 , decode(nvl(T15,0),0,'-',to_char(T15)) T15 , decode(nvl(T16,0),0,'-',to_char(T16)) T16 , decode(nvl(T17,0),0,'-',to_char(T17)) T17 , decode(nvl(T18,0),0,'-',to_char(T18)) T18 , decode(nvl(T19,0),0,'-',to_char(T19)) T19 , decode(nvl(T20,0),0,'-',to_char(T20)) T20 , decode(nvl(T21,0),0,'-',to_char(T21)) T21 , decode(nvl(T22,0),0,'-',to_char(T22)) T22 , decode(nvl(T23,0),0,'-',to_char(T23)) T23 , decode(nvl(T24,0),0,'-',to_char(T24)) T24 from ( select sum( decode( nvl(to_char(lh.FIRST_TIME,'yyyymmddhh24'),0) ,0,0 ,1) ) as slog , dr.dr as slday , dr.dh as slhour --, to_char(lh.FIRST_TIME,'yyyymmddhh24') from v$log_history lh ,( select td.dr||th.hr as dg , th.hr as dh , td.dr as dr from (select ltrim(to_char(rownum,'09')) as hr from all_objects where rownum < 25) th , (select ltrim(to_char(sysdate-(rownum-1),'yyyymmdd')) as dr from all_objects where rownum < 20) td ) dr where dr.dg = to_char(lh.FIRST_TIME (+),'yyyymmddhh24') group by to_char(lh.FIRST_TIME,'yyyymmddhh24') , dr.dg ,dr.dh,dr.dr ) pivot ( sum (slog) FOR slhour IN ('01' AS T01 ,'02' AS T02 ,'03' AS T03 ,'04' AS T04 ,'05' AS T05 ,'06' AS T06 ,'07' AS T07 ,'08' AS T08 ,'09' AS T09 ,'10' AS T10 ,'11' AS T11 ,'12' AS T12 ,'13' AS T13 ,'14' AS T14 ,'15' AS T15 ,'16' AS T16 ,'17' AS T17 ,'18' AS T18 ,'19' AS T19 ,'20' AS T20 ,'21' AS T21 ,'22' AS T22 ,'23' AS T23 ,'24' AS T24 ) ) / Komplettes Beispiel siehe hier [[https://orapowershell.codeplex.com/SourceControl/latest#sql/redo.sql|redo.sql]] Ergebniss: {{:prog:redo_log_switch_diagramm.png?500|Redo Log Switch Diagramm - SQL Script}} Als Pivot Tabelle für das Datum dienen die beiden Abfragen th, td mit der für die letzen 20 Tage alle Stunden aufgelistet werden, über den Outjoin mit der v$log_history werden dann die Logs pro Stunde und Tag gezählt. Das Ergebnis wird nach rechts mit dem Pviot Statement transponiert. ---- ==== Preise mit gestaffelten Rabatten berechnen===== Rabatte sind gestaffelt und sollen mehrstufig in einer Zeile angezeigt werden. Der Rabattwert in % wird dabei je nach Stufe abgezogen, das End Ergebnis soll angezeigt werden. drop table T_RABAT; create table T_RABAT ( nr number, pos number, beitrag number, sum_beitrag number, rabat number,rabat_row number); insert into T_RABAT values (10 ,2200 ,115.3 ,513.83136 ,-3.68 ,1 ); insert into T_RABAT values (10 ,2200 ,115.3 ,513.83136 ,-20 ,2 ); insert into T_RABAT values (10 ,2200 ,115.3 ,513.83136 ,-5 ,3 ); insert into T_RABAT values (10 ,2200 ,115.3 ,513.83136 ,-10 ,4 ); insert into T_RABAT values (10 ,3100 ,13.16 ,513.83136 ,-5 ,1 ); insert into T_RABAT values (10 ,3100 ,13.16 ,513.83136 ,-3.68 ,2 ); insert into T_RABAT values (10 ,3100 ,13.16 ,513.83136 ,-10 ,3 ); insert into T_RABAT values (10 ,3100 ,13.16 ,513.83136 ,-20 ,4 ); commit; select nr , beitrag ,(Beitrag -((Beitrag / 100 )*nvl( abs(RABATT1),0))) as beitrag1 , RABATT2 , RABATT3 , RABATT4 , RABATT5 as gesamt_preis from ( select NR , POS , BEITRAG , SUM_BEITRAG , RABATT1 , RABATT2 , RABATT3 , RABATT4 , RABATT5 from ( SELECT NR , POS , BEITRAG , SUM_BEITRAG , rabat , rabat_row FROM T_RABAT GROUP by NR , POS , BEITRAG , SUM_BEITRAG , rabat , rabat_row ) pivot ( MAX (rabat) FOR rabat_ROW IN ('1' AS RABATT1 ,'2' AS RABATT2 ,'3' AS RABATT3 ,'4' AS RABATT4 ,'5' as RABATT5 ) ) ) ----------------------- select nr , beitrag , beitrag1 , beitrag2 , beitrag3 , beitrag4 , (beitrag4 -((beitrag4 / 100 )*nvl( abs(RABATT5),0))) as gesamtpreis from ( select nr , beitrag , beitrag1 , beitrag2 , beitrag3 , (beitrag3 -((beitrag3 / 100 )*nvl( abs(RABATT4),0))) as beitrag4 , RABATT5 from ( select nr , beitrag , beitrag1 , beitrag2 , (beitrag2 -((beitrag2 / 100 )*nvl( abs(RABATT3),0))) as beitrag3 , RABATT4 , RABATT5 from ( select nr , beitrag , beitrag1 , (beitrag1 -((beitrag1 / 100 )*nvl( abs(RABATT2),0))) as beitrag2 , RABATT3 , RABATT4 , RABATT5 from ( select nr , beitrag ,(Beitrag -((Beitrag / 100 )*nvl( abs(RABATT1),0))) as beitrag1 , RABATT2 , RABATT3 , RABATT4 , RABATT5 from ( select NR , POS , BEITRAG , SUM_BEITRAG , RABATT1 , RABATT2 , RABATT3 , RABATT4 , RABATT5 from ( SELECT NR , POS , BEITRAG , SUM_BEITRAG , rabat , rabat_row FROM T_RABAT GROUP by NR , POS , BEITRAG , SUM_BEITRAG , rabat , rabat_row ) pivot ( MAX (rabat) FOR rabat_ROW IN ('1' AS RABATT1 ,'2' AS RABATT2 ,'3' AS RABATT3 ,'4' AS RABATT4 ,'5' as RABATT5 ) ) ) ) ) ) ) ; -- Ergebnis NR BEITRAG BEITRAG1 BEITRAG2 BEITRAG3 BEITRAG4 GESAMTPREIS ------------ ------------ ------------ ------------ ------------ ------------ ------------ 10 115.3 111.05696 88.845568 84.4032896 75.96296064 75.96296064 10 13.16 12.502 12.0419264 10.83773376 8.670187008 8.670187008 ==== Quellen ==== * http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html * http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#CHDCEJJE