Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:sql_oracle_pivot

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

 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:
<PivotSet>
 <item><COLUMN name = "DNAME">ACCOUNTING</column><COLUMN name = "COUNTER">296</column></item>
 <item><COLUMN name = "DNAME">OPERATIONS</column><COLUMN name = "COUNTER"></column></item>
 <item><COLUMN name = "DNAME">RESEARCH</column>  <COLUMN name = "COUNTER">5</column></item>
 <item><COLUMN name = "DNAME">SALES</column>     <COLUMN name = "COUNTER">6</column></item>
</PivotSet>

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 redo.sql

Ergebniss: 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

Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
prog/sql_oracle_pivot.txt · Zuletzt geändert: 2019/07/29 22:39 von Gunther Pippèrr