Pivot direkt in Oracle

[steyrblade]

Hallo zusammen,

ich würde gerne eine View erstellen, welche eine Pivot eines Selects ist.
Auf http://www.tutorials.de/relationale-datenbanksysteme/306245-sql-ausgabe-spalten.html
steht schon etwas ähnliches nur das dort nur der Inhalt in eine Spalte eingetragen wird.
Sonst würde diese Abfrage für mich bereits passen.


Ich habe folgenden SELECT:

SELECT STATION, COUNT(X) FROM XY 
GROUP BY STATION
ORDER BY STATION;

und bekomme z.B. 20 Zeilen ausgegeben

Station1 30
Station2 10
Station3 45
...

Ich hätte nun gerne davon eine Pivot (als View) erstellt.

Station1 Station2 Station3
  30        10       45

Den Stationnamen (hier Station1 Station2 Station3) als Spalten Name.

Danke für eure Hilfe.

 

[MPr]

Hallo,
wenn die Anzahl der Spalten bekannt ist, kann man das über CASE oder DECODE lösen:

select sum(case when deptno = 10 then emp_count else null end) dept10
     , sum(case when deptno = 20 then emp_count else null end) dept20
     , sum(case when deptno = 30 then emp_count else null end) dept30
  from (select deptno, count(*) emp_count
          from emp
         group by deptno );

DEPT10     DEPT20     DEPT30
------ ---------- ----------
     3          5          6

Für nicht statische Fälle könnte man dynamisches SQL verwenden, wie es der Herr Czarski hier tut: http://sql-plsql-de.blogspot.com/2007/04/kreuztabellen-jetzt-wird-es-generisch.html

In Oracle 11 gibt's PIVOT/UNPIVOT als SQL-Erweiterung - wozu man wiederum beim Herrn Czarski eine Einführung findet (dessen Blog ohnehin ziemlich lesenswert ist): http://sql-plsql-de.blogspot.com/2007/08/kreuztabellen-in-oracle11g-sql-pivot.html.

Gruß
MP

 

[steyrblade]

Hallo MPr,

mit
http://sql-plsql-de.blogspot.com/200...generisch.html
sieht es schon sehr gut aus.

Was mir hier aber noch fehlt ist, dass die Spaltennamen den Namen des X-Wertes annehmen und nicht ein indiziertes VALUE als Namen.

with data as (
  SELECT y_spalte, x_spalte, COUNT(WERT) WERT_COUNT FROM XYZ GROUP BY y_spalte, x_spalte
), x_dist_values as (
  select distinct x_spalte val from data order by 1
), x_values_rownum as (
  select rownum zeile, val from x_dist_values where rownum <= 10
)
select distinct 
  data.y_spalte,
SUM(  case when x.zeile = 1 then WERT_COUNT else null end) as "VALUE[1]",
SUM(  case when x.zeile = 2 then WERT_COUNT else null end) as "VALUE[2]",
SUM(  case when x.zeile = 3 then WERT_COUNT else null end) as "VALUE[3]",
SUM(  case when x.zeile = 4 then WERT_COUNT else null end) as "VALUE[4]",
SUM(  case when x.zeile = 5 then WERT_COUNT else null end) as "VALUE[5]",
SUM(  case when x.zeile = 6 then WERT_COUNT else null end) as "VALUE[6]",
SUM(  case when x.zeile = 7 then WERT_COUNT else null end) as "VALUE[7]",
SUM(  case when x.zeile = 8 then WERT_COUNT else null end) as "VALUE[8]",
SUM(  case when x.zeile = 9 then WERT_COUNT else null end) as "VALUE[9]",
SUM(  case when x.zeile = 10 then WERT_COUNT else null end) as "VALUE[10]"
from data
join x_values_rownum x on (data.x_spalte= x.val)
group by data.y_spalte;

Anstatt dem "VALUE[X]" String hätte ich nun gerne den Value der aktuellen x_spalte.
Geht das?
Wenn ja wie müsste die Variable aussehen die ich dafür eintragen muss?

 

[MPr]

da die Inhalte der x_spalte beim Zusammenbauen der Query nicht bekannt sind, kann man sie nicht ohne Weiteres ergänzen. Dazu müsste man die Label entweder über einen zusätzlichen Parameter übergeben oder erst aus der Datenbank abfragen und dann ergänzen. Für die zweite Variante findet man bei Tom Kyte ein sehr kompaktes Beispiel: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:15151874723724

Wieder mit emp wäre z.B. Folgendes möglich:

create or replace
procedure create_pivot
is
    l_stmt varchar2(2000);
begin

    l_stmt := 'select job';
    for x in ( select distinct deptno from emp order by 1 )
    loop
        l_stmt := l_stmt || ', sum(case when deptno = ' || x.deptno || ' then sal else 0 end) as sal_dept' || x.deptno;
    end loop;
    l_stmt := l_stmt || ' from emp group by job order by job';

    dbms_output.put_line(l_stmt);
end;
/

SQL>       exec create_pivot
-- die Procedure liefert das Ergebnis in einer Zeile
select job
     , sum(case when deptno = 10 then sal else 0 end) as sal_dept10
	 , sum(case when deptno = 20 then sal else 0 end) as sal_dept20
	 , sum(case when deptno = 30 then sal else 0 end) as sal_dept30
  from emp 
 group by job 
 order by job

JOB       SAL_DEPT10 SAL_DEPT20 SAL_DEPT30
--------- ---------- ---------- ----------
ANALYST            0       6000          0
CLERK           1300       1900        950
MANAGER         2450       2975       2850
PRESIDENT       5000          0          0
SALESMAN           0          0       5600

Das ist dann schon recht übersichtlich, könnte aber natürlich noch mit Parametrisierung versehen werden. Der einzige Nachteil ist, dass man hier zwei Query-Zugriffe benötigt.

Gruß
MP

 

[steyrblade]

Wenn ich nun davon ausgehe, dass die Spaltenzahl fest ist, und ich diese Daten dann als View bzw. mit einem Insert in eine Tabelle schreiben möchte, wie würde das dann konkret aussehen.

INSERT INTO TabelleX (
with data as (
  SELECT y_spalte, x_spalte, COUNT(WERT) WERT_COUNT FROM XYZ GROUP BY y_spalte, x_spalte
), x_dist_values as (
  select distinct x_spalte val from data order by 1
), x_values_rownum as (
  select rownum zeile, val from x_dist_values where rownum <= 10
)
select distinct 
  data.y_spalte,
SUM(  case when x.zeile = 1 then WERT_COUNT else null end) as "VALUE[1]",
SUM(  case when x.zeile = 2 then WERT_COUNT else null end) as "VALUE[2]",
SUM(  case when x.zeile = 3 then WERT_COUNT else null end) as "VALUE[3]",
SUM(  case when x.zeile = 4 then WERT_COUNT else null end) as "VALUE[4]",
SUM(  case when x.zeile = 5 then WERT_COUNT else null end) as "VALUE[5]",
SUM(  case when x.zeile = 6 then WERT_COUNT else null end) as "VALUE[6]",
SUM(  case when x.zeile = 7 then WERT_COUNT else null end) as "VALUE[7]",
SUM(  case when x.zeile = 8 then WERT_COUNT else null end) as "VALUE[8]",
SUM(  case when x.zeile = 9 then WERT_COUNT else null end) as "VALUE[9]",
SUM(  case when x.zeile = 10 then WERT_COUNT else null end) as "VALUE[10]"
from data
join x_values_rownum x on (data.x_spalte= x.val)
group by data.y_spalte);

bzw.

CREATE VIEW TabelleXY AS (
with data as (
  SELECT y_spalte, x_spalte, COUNT(WERT) WERT_COUNT FROM XYZ GROUP BY y_spalte, x_spalte
), x_dist_values as (
  select distinct x_spalte val from data order by 1
), x_values_rownum as (
  select rownum zeile, val from x_dist_values where rownum <= 10
)
select distinct 
  data.y_spalte,
SUM(  case when x.zeile = 1 then WERT_COUNT else null end) as "VALUE[1]",
SUM(  case when x.zeile = 2 then WERT_COUNT else null end) as "VALUE[2]",
SUM(  case when x.zeile = 3 then WERT_COUNT else null end) as "VALUE[3]",
SUM(  case when x.zeile = 4 then WERT_COUNT else null end) as "VALUE[4]",
SUM(  case when x.zeile = 5 then WERT_COUNT else null end) as "VALUE[5]",
SUM(  case when x.zeile = 6 then WERT_COUNT else null end) as "VALUE[6]",
SUM(  case when x.zeile = 7 then WERT_COUNT else null end) as "VALUE[7]",
SUM(  case when x.zeile = 8 then WERT_COUNT else null end) as "VALUE[8]",
SUM(  case when x.zeile = 9 then WERT_COUNT else null end) as "VALUE[9]",
SUM(  case when x.zeile = 10 then WERT_COUNT else null end) as "VALUE[10]"
from data
join x_values_rownum x on (data.x_spalte= x.val)
group by data.y_spalte);

geht leider nicht.

SQL-Fehler: ORA-32034: unsupported use of WITH clause
32034. 00000 -  "unsupported use of WITH clause"
*Cause:    Inproper use of WITH clause because one of the following two reasons
           1. nesting of WITH clause within WITH clause not supported yet
           2. For a set query, WITH clause can't be specified for a branch.
           3. WITH clause can't sepecified within parentheses.
*Action:   correct query and retry

(Oracle 10.2 läuft auf unserem Server)

 

[MPr]

das ist vermutlich nur ein Klammerungsproblem (soll heißen: die Klammern vor dem WITH und vor dem abschließenden Semikolon stören):

SQL> create view test_view
  2  as
  3  (with
  4   data as (select * from emp)
  5  select * from data);
select * from data)
                  *
FEHLER in Zeile 5:
ORA-32034: Nicht unterstützte Benutzung von WITH-Klausel

SQL> r
  1  create view test_view
  2  as
  3  with
  4  data as (select * from emp)
  5* select * from data

View wurde erstellt.

Abgesehen davon ist die WITH-Klausel nur ein Ersatz für eine Subquery:

SQL> select distinct deptno
  2    from (select * from emp);

    DEPTNO
----------
        30
        20
        10

Abgelaufen: 00:00:00.03
SQL> with
  2  data as (select * from emp)
  3  select distinct deptno from data;

    DEPTNO
----------
        30
        20
        10

Man kann das WITH also auch vermeiden. Die WITH-Klausel (aka CTE = Common Table Expression (so nennt man das Feature bei ANSI); aka Subquery Factoring (so der Oracle-Terminus)) dient eigentlich zur einmaligen Definition wiederkehrender Subqueries, kann aber auch zur Verbesserung der Lesbarkeit von SQL-Code genutzt werden.

Gruß

MP