Spalten zu Zeilen

[gonzolino]

Hallo zusammen

Ich stehe gerade vor einem Problem mit PL/SQL bei dem ich um eure Hilfe bitte:

Pro Monat werden in einer Tabelle die Kapazitäten von Mitarbeitern festgehalten. Eine Abfrage sollte nun eine Übersicht bieten, in der die Kapazitäten nicht zeilen, sondern spaltenweise dargestellt werden.


Quelltabelle:

PERSON_GID REPORTINGDATE PERSONNELCAPACITY
---------- ------------- -----------------
  14145214 01-JAN-15                     1 
  14145214 01-DEC-14                     1 
  14145214 01-NOV-14                     1 
  14145214 01-OCT-14                     1 
  14145214 01-SEP-14                     1 
  14145214 01-AUG-14                     1 
  14145214 01-JUL-14                     1 
  14145214 01-JUN-14                     1 
  14145214 01-MAY-14                     1 
  14145214 01-APR-14                     1 
  14145214 01-MAR-14                     1 
  14145214 01-FEB-14                     1 
  14145214 01-JAN-14                     1 
  11412587 01-JAN-15                    .9 
  11412587 01-DEC-14                    .9 
  11412587 01-NOV-14                    .9 
  11412587 01-OCT-14                    .9 
  11412587 01-SEP-14                    .9 
  11412587 01-AUG-14                    .9 
  11412587 01-JUL-14                    .9 
  11412587 01-JUN-14                     1 
  11412587 01-MAY-14                     1 
  11412587 01-APR-14                     1 
  11412587 01-MAR-14                     1 
  11412587 01-FEB-14                     1 
  11412587 01-JAN-14                     1

Gewünschte Darstellung:

PERSON_GID 01-JAN-14 01-FEB-14 01-MAR-14 01-APR-14 01-MAY-14 01-JUN-14 01-JUL-14 01-AUG-14 01-SEP-14 01-OCT-14 01-NOV-14 01-DEC-14 01-JAN-15
---------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- ---------
14145214   1         1         1         1         1         1         1         1         1         1         1         1         1
11412587   1         1         1         1         1         1         0.9       0.9       0.9       0.9       0.9       0.9       0.9

Ich habe mir diverse Beispiele im Web angesehen aber irgendwie bekomme ich das nicht hin. Wir nutzen Oracle 11g und da gibt es ja die PIVOT-Funktion für Kreuztabellen aber da komme ich auch nicht weiter.

Hinzu kommt zu obiger Forderung, dass ich die Anzahl der angezeigten Monatsspalten auswählbar machen muss. D.h. ich muss angeben können, welche/r Monat/e ich in der Übersicht darstellen will (quartalsweise, jährlich, etc.).

Kann mir jemand einen Tipp geben wie ich das hinbekomme?

Besten Dank für eure Hilfe.

Grüsse
Gonzolino

[defo]

Pivot ist schon richtig. Zeig doch mal, was Du hast bzw. an welcher Stelle es hakt.
Wählbare Spalten ist eher blöd, weil Du die schon fest im Statement angeben musst. Hier wäre aber evtl auf Select Ebene die Möglichkeit, Spalten einfach auszublenden. Kommt auf den Client an.

[gonzolino]

Hallo Defo

Unterdessen bin ich tatsächlich einen Schritt weiter gekommen. Hatte offenbar einen Fehler in der Abfrage. Das hier habe ich nun:

select * from (
  select PERSON_GID, REPORTINGDATE, PERSONNELCAPACITY from variables
  where PERSON_GID = 14145214 AND STATISTIK_ID = 2 
  OR PERSON_GID = 11412587 AND STATISTIK_ID = 2
)
PIVOT (
  SUM(PERSONNELCAPACITY)
  FOR REPORTINGDATE
  IN (TO_DATE('01.01.2014','dd.mm.yyyy') AS "01.01.2014",
      TO_DATE('01.02.2014','dd.mm.yyyy') AS "01.02.2014",
      TO_DATE('01.03.2014','dd.mm.yyyy') AS "01.03.2014",
      TO_DATE('01.04.2014','dd.mm.yyyy') AS "01.04.2014",
      TO_DATE('01.05.2014','dd.mm.yyyy') AS "01.05.2014",
      TO_DATE('01.06.2014','dd.mm.yyyy') AS "01.06.2014",
      TO_DATE('01.07.2014','dd.mm.yyyy') AS "01.07.2014",
      TO_DATE('01.08.2014','dd.mm.yyyy') AS "01.08.2014",
      TO_DATE('01.09.2014','dd.mm.yyyy') AS "01.09.2014",
      TO_DATE('01.10.2014','dd.mm.yyyy') AS "01.10.2014",
      TO_DATE('01.11.2014','dd.mm.yyyy') AS "01.11.2014",
      TO_DATE('01.12.2014','dd.mm.yyyy') AS "01.12.2014",
      TO_DATE('01.01.2014','dd.mm.yyyy') AS "01.01.2015"
  )
);

Damit erhalte ich folgendes:

PERSON_GID 01.01.2014 01.02.2014 01.03.2014 01.04.2014 01.05.2014 01.06.2014 01.07.2014 01.08.2014 01.09.2014 01.10.2014 01.11.2014 01.12.2014 01.01.2015
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
  11412587          1          1          1          1          1          1         .9         .9         .9         .9         .9         .9          1 
  14145214          1          1          1          1          1          1          1          1          1          1          1          1          1

Die Werte müssen bei PIVOT aggregiert werden. Habe hier im Beispiel SUM verwendet aber das spielt keine Rolle, da pro Monat immer nur ein Wert pro Mitarbeiter vorhanden sein kann. Somit hätte ich zumindest mal die Spaltendarstellung erreicht. Gibt's aus deiner Sicht etwas das ich optimieren oder verbessern könnte?

Nun besteht aber noch das Problem mit der Auswählbarkeit der Monate.

Grüsse
Gonzolino

[defo]

In Spalte Dezember 2015 hast Du 2014 als Bedingung drin stehen.
Die Persongid würde ich vielleicht in ein "in (,,)" packen statt "or" zu verwenden.

Was die Spalten angeht, kommt es wie gesagt auf den Einsatz an. Welcher Client, welche Auswahlmöglichkeiten.. was verwendest Du da?
Am billigesten ist ein äußeres Selekt, das nur gewählte Spalten heranzieht aus den aufbereiteten. Ist vielleicht nicht unbedingt Resourcen schonend.
Oder Du baust das ganze Statement bzw. die In clause dynamisch zusammen.

[gonzolino]

Danke für den Hinweis. Da habe ich tatsächlich das falsche Jahr gewählt. Die IN Klausel kannte ich bisher gar nicht und ist ja um einiges einfacher :-)

select * from (
  select PERSON_GID, REPORTINGDATE, PERSONNELCAPACITY from variables
  where PERSON_GID IN (14145214,11412587)
  AND STATISTIK_ID = 2 
)
PIVOT (
  SUM(PERSONNELCAPACITY)
  FOR REPORTINGDATE
  IN (TO_DATE('01.01.2014','dd.mm.yyyy') AS "01.01.2014",
      TO_DATE('01.02.2014','dd.mm.yyyy') AS "01.02.2014",
      TO_DATE('01.03.2014','dd.mm.yyyy') AS "01.03.2014",
      TO_DATE('01.04.2014','dd.mm.yyyy') AS "01.04.2014",
      TO_DATE('01.05.2014','dd.mm.yyyy') AS "01.05.2014",
      TO_DATE('01.06.2014','dd.mm.yyyy') AS "01.06.2014",
      TO_DATE('01.07.2014','dd.mm.yyyy') AS "01.07.2014",
      TO_DATE('01.08.2014','dd.mm.yyyy') AS "01.08.2014",
      TO_DATE('01.09.2014','dd.mm.yyyy') AS "01.09.2014",
      TO_DATE('01.10.2014','dd.mm.yyyy') AS "01.10.2014",
      TO_DATE('01.11.2014','dd.mm.yyyy') AS "01.11.2014",
      TO_DATE('01.12.2014','dd.mm.yyyy') AS "01.12.2014",
      TO_DATE('01.01.2015','dd.mm.yyyy') AS "01.01.2015"
  )
);

Was den Client angeht, da nutze ich den SQL Developer. Schlussendlich soll aber eine Webanwendung die Auswertungen aufrufen. Den Developer nutze ich im Moment nur um zu prüfen, ob ich mit meinem aktuellen DB-Modell alle Auswertungen durchführen kann, die benötigt werden.

Ein äusseres Skelet für die benötigten Spalten ist natürlich eine Möglichkeit, aber dann muss ich in PIVOT in der IN-Klausel die Spalten immer noch irgendwie statisch bekannt machen oder übersehe ich da etwas? Dynamisch stehe ich da total auf dem Schlauch. Habe versucht mich da ein wenig schlau zu machen aber irgendwie begreife ich das nicht.

[defo]

Mit dynamisch meinte ich nicht dynamische sql Statement Generierung aus Oracle, sondern einfach im Client (Deine Webanwendung) das Statement zusammen bauen. Du brauchst ja nur eine Spalte Distinct abzufragen, um die darstellbaren neuen Spalten zu erhalten und zur Auswahl anzubieten. Aus den gewählten Spalten setzt Du die In Clause und die Select Clause zusammen.
Wie weit Deine Darstellungsschicht dann mit den variierenden Spalten klar kommt, hängt davon ab, was Du zur Visualisierung nutzt. Ggf musst Du also nicht nur das Statement entsprechend der ausgewählten Spalten zusammensetzen, sondern auch Deiner GUI mitteilen, was dann für Spalten ankommen bzw angezeigt werden sollen.

[gonzolino]

Ok. Jetzt ist klar was du meinst.

Schlussendlich wird mir wohl nichts anderes übrig bleiben, als die Statements in der Anwendung zusammen zu bauen. Dann bin ich zumindest flexibel. Schön wäre es natürlich gewesen, wenn ich alles Abfragen bereits auf DB-Ebene hätte hinterlegen können. Der Hintergedanke hierbei war folgender:
Die Abteilung für die ich die Anwendung schreiben werde, wird mit Sicherheit in naher Zukunft Wünsche für weitere Abfragen haben. Da habe ich mir gedacht es wäre praktisch, die Abfrage auf der DB zu hinterlegen (Views, Prozeduren, etc.) und diese lediglich in der Anwendung aufzulisten. Bei entsprechender Auswahl wäre dann einfach das Resultat in eine Excel-Tabelle exportiert worden (eine Darstellung in der View wird nicht nötig sein, da die Abteilung die Resultate sowieso noch in Excel aufhübschen will). Somit wäre mir dann der Aufwand erspart geblieben, bei jeder neuen Abfrage die Anwendung anzupassen. Ich hoffe man versteht was ich da tun wollte.

[defo]

Naja, Du kannst natürlich Abfragen / Views vorbereiten, die können aber naturgemäß immer nur einen Teil der interaktiven Nutzerauswahl abbilden.
Wenn ein Query die Zahlen, also die Spalten fertig für 2015 liefert, kann der Nutzer daraus eine Auswahl treffen, ebenso für 2014 usw. macht 10 Views für die letzten 10 jahre. Analog für einen Zeitraum, der die Geschäftsjahre der Firma abbildet. Alle diese Abfragen liefern im Zweifel 11 Spalten zu viel, wenn der User nur 1 Monat wählt. Andererseits helfen sie nicht weiter, wenn der gewünschte Darstellllungszeitraum einen Jahreswechsel enthält.
Fertige Views hinterlegen wäre ja auch gerade dann okay, wenn sowieso eine manuelle Nachverarbeitung in Excel erfolgt. Wieso muss man dann noch extra Mai und Juni odersowas auswählen? Vielleicht ein kleines Luxusproblem oder?


Falls Du noch weiter mit SQL experimentieren möchtest und eh mit Webclient arbeitest, kannst Du Dir auch die XML ..Any Variante von PIVOT anschauen. Ergebnisse sind dann halt in XML, ANY sagt aus, dass alle Werte abgeholt/transformiert werden sollen, also kein 'mühsamer' Aufbau von IN ...
Glaub in Kombi mit XML gibt's neben ANY auch noch eine Subselect Variante, was aber wohl Dein Problem nur an eine andere Stelle verschiebt.