===== 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