Dynamische Pivot

Monarch

Benutzer
Beiträge
17
Hallo Experten,

ich habe folgenden Code der eine String erstellet:
-----------------------------------------------------------------------------------------
DECLARE
cols VARCHAR2(2000);
xQuery varchar2(2000);
BEGIN
cols :='';
for cCols in ( SELECT '''' || Spalte || '''' Spalte
FROM (SELECT DISTINCT Spalte
FROM IN2LOGSP ))
loop
if cols is not null then
cols := cols || ',';
end if;
cols := cols || ccols.spalte;
end loop;

xQuery := 'SELECT * FROM (SELECT timestamp, Spalte, wert FROM IN2LOGSP) PIVOT ( max(wert) FOR Spalte IN (' || Cols || ') ) ORDER BY timestamp';

END
-----------------------------------------------------------------------------------------
xQuery würde zum Beispiel dann so aussehen:

SELECT * FROM (SELECT timestamp, Spalte, wert FROM IN2LOGSP) PIVOT ( max(wert) FOR Spalte IN ('BEITR_RZV','BEITR_RZV_Z','BEITR_BUZ_Z','BEITR_HV_Z','NAME1','BEITR_BUZ','BEITR_UZV','BEITR_UZV_Z','RABATTSTUFE','BEITR_HV') ) ORDER BY timestamp
-----------------------------------------------------------------------------------------
Jetzt das Problem, ich würde gerne ein View haben "Select * from ????". Wie mache ich das?!?! Wo und wie lege ich mein Pivot-Code in Oracle ab damit ich mit einer View darauf zugreifen kann ?

Vielen Dank Monarch
(Ja ist ein Name, wenn man aus Südafrika kommt. Er bezieht sich auf einen Falter)
 
Werbung:
Hallo drdimitri
Oracle 11..2.030 64 bit und wenn es sein muss darf ich alles.

Das Problem ist auch, dass alles was ich bis dato zum Thema gefunden habe, immer ein vordefiniertes Objekt (Table, record, Pipeline etc) hat. Da aber mein Code eine dynamische Anzahl von Spalten hat habe ich da meine Probleme

Monarch
 
Ich hätte jetzt auch eine Pipelined Function vorgeschlagen, aber wenn sich die Struktur jederzeit ändern kann wird das nichts.
Das ist alles mit DDL verbunden. Eine Möglichkeit wäre noch, die Daten zu konkatenieren und nur eine Spalte in der Pipelined Funktion zurückzugeben, quasi ein csv erzeugen und die Anwendung parst das dann wieder.
Bei 4000 Zeichen ist hier aber Schluss sofern Du nicht auf CLOB umsteigen möchtest

PS: Mit LISTAGG kannst deine Spaltenliste deutlich einfacher ohne Schleife erstellen.
 
Ähm, sorry drdimitri,

aber es ist doch logisch das sich die Struktur ändert, wäre dem nicht so, würde ein dynamische Pivot ja keinen Sinn ergeben. Und jetzt mal im Ernst ohne das ich Oracle schlecht machen will, aber im SQL-Server ist dies mit einer Zeile Code möglich. Es kann doch nicht sein, dass das im Oracle mehr oder weniger unmöglich ist!
 
Hallo drdimitri, hallo unficyp

als erstes zu unficyp: Dein Vorschlag funzt auch nicht, weil die Spaltenanzahl auch bei dir vorher bekannt sein muss, ist es aber nicht in meiner Problemstellung.

jetzt zu drdimitri: Im SQL Server kann ich in aus jeder Procedure ein Select zurückgeben in dem man diesen z.B. als letztes in den Procedurcode schreibe. Das geht auch in einer Funktion.

Aber egal: Ich werde bei Änderung in der Basistabelle IN2LOGSP (siehe code oben) eine entsprechende View (Create ...) jedesmal neu erstellen müssen, welche das Pivot zurückgibt.
 
Hallo drdimitri,

dein Link gelesen, aber wahrscheinlich nicht ganz verstanden... Also ich habe jetzt folgenden Code in eine Funktion gepackt:
----------------------------------------------------------------------------------
Function Log_Pivot return sys_refcursor is
v_rc sys_refcursor;
cols VARCHAR2(2000);
xQuery varchar2(2000);
BEGIN
cols :='';
for cCols in ( SELECT '''' || Spalte || '''' Spalte
FROM (SELECT DISTINCT Spalte
FROM IN2LOGSP ))
loop
if cols is not null then
cols := cols || ',';
end if;
cols := cols || ccols.spalte;
end loop;

-- xQuery := 'SELECT * FROM (SELECT timestamp, Spalte, wert FROM IN2LOGSP) PIVOT ( max(wert) FOR Spalte IN (' || Cols || ') ) ORDER BY timestamp';
open v_rc for 'SELECT * FROM (SELECT timestamp, Spalte, wert FROM IN2LOGSP) PIVOT ( max(wert) FOR Spalte IN (' || Cols || ') ) ORDER BY timestamp';

RETURN v_rc;
END Log_Pivot;
----------------------------------------------------------------------------------
So weit so gut, aber wie greife ich jetzt über eine VIEW (Select ... ) darauf zu!
 
Für den Zugriff brauchst du eine Anwendung also eine Anwendung, das die Funktion aufruft oder auch ein fertiges Programm dass den Cursor auslesen kann (Toad, SQL Developer etc).

Code:
create or replace function fkt_test return sys_refcursor is
  c sys_refcursor;
begin
open c for 'select 1234 as col from dual';
return c;
end;
/
Dann in Toad/SQLDeveloper etc:

Code:
select fkt_test from dual;

Alternativ in sqlplus:
Code:
SQL> var mycursor refcursor
SQL> exec select fkt_test into :mycursor from dual;

PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> print mycursor

       COL
----------
      1234
Mittels spool und diversen Formatierungsparametern kann man das dann auch in eine Textdatei umleiten falls man die Ausgabe automatisiert ausgeben möchte.
 
Hmmm, funzt auch nicht ...

Also der Query/Cursor der in der Funktion Log_Pivot erzeugt sieht so aus:

SELECT * FROM (SELECT timestamp, Spalte, wert FROM IN2LOGSP) PIVOT ( max(wert) FOR Spalte IN ('BEITR_RZV','BEITR_RZV_Z') ) ORDER BY timestamp

Führe ich diesen direkt im SQL Develloper aus gibt dieser folgendes zurück:

upload_2017-6-6_10-4-37.png

Greife ich über die Funktion zu auch im SQL Develloper:

Select IN2LOG.LOG_PIVOT from dual;

Sieht das so aus

{<TIMESTAMP=02.06.17 09:40:25,700000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 09:51:17,432000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 09:56:50,652000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 09:56:50,667000000,'BEITR_RZV'=0,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 09:57:23,808000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 09:59:43,316000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 10:26:17,129000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 10:26:57,645000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 11:13:38,695000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 11:13:38,711000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 11:19:28,180000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 12:05:51,749000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 12:05:51,765000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=1>,<TIMESTAMP=02.06.17 12:56:52,562000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 12:56:52,577000000,'BEITR_RZV'=0,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 13:04:35,334000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=0>,<TIMESTAMP=02.06.17 13:05:02,811000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=1>,<TIMESTAMP=02.06.17 13:05:49,203000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 13:06:01,515000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 13:06:11,359000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=0>,<TIMESTAMP=02.06.17 13:37:28,985000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 13:37:29,000000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 13:49:46,111000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 13:49:46,127000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 14:14:26,936000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 14:25:49,534000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 14:25:49,550000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 14:30:36,086000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 14:30:36,102000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 16:08:56,111000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 16:08:56,127000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 17:01:07,801000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=02.06.17 17:01:27,880000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=06.06.17 07:25:52,409000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=06.06.17 07:25:52,425000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=06.06.17 07:32:04,268000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=06.06.17 07:32:04,284000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,<TIMESTAMP=06.06.17 07:36:57,943000000,'BEITR_RZV'=null,'BEITR_RZV_Z'=null>,}

Wo liegt mein Denkfehler ...
 
Zuletzt bearbeitet:
Das ist kein Denkfehler, so bereitet der SQL Developer das eben auf (sieht nicht sonderlich schön aus).
In Toad ist es deutlich besser, aber das meinte ich damit: Du bekommst einen Cursor zurück, also ein fix und fertiges Ergebnis und die Anwendung (in dem Fall SQLDev) ließt die Metainformationen aus wie das Ergebnis aufgebaut ist, und baut dann eine (schlechte) Anzeige.

Wenn du eine bessere visuelle Aufbereitung haben möchtest, dann kannst Du:
- Eine andere Oberfläche verwenden
- Eine eigene Anwendung schreiben, die dir das in die gewünschte Form bringt (in Java würdest Du ein ResultSet bekommen, in VB ein RecordSet)
 
OK, dann sag ich jetzt erstmal Danke für deine Geduld. Ich schaue mal ob in der Anwendung über ein Recordset, dass besser funzt
 
Werbung:
Hallo Leute hier die meine Lösung für ein dynamisches Pivot: Bitte beachten das die Tabelle "IN2LOGSP" entsprechend durch eure Tabelle ersetzt werden muss (siehe DeinTabelle). Weiter muss festgelegt werden aus welcher Spalte die Pivot - Spalten erstellt werden soll (siehe DeineSpalte)

CREATE OR REPLACE PROCEDURE IN2LOGPIVOT
( s_cur IN OUT SYS_REFCURSOR )
IS
TYPE r_cur_t IS REF Cursor;
r_cur r_cur_t;
xQuery VARCHAR2(2000);
cols VARCHAR2(2000);
BEGIN
cols :='';
--Liest alle Wert die im Pivot als Spalten dargestellt werden aus
for cCols in ( SELECT '''' || DeineSpalte || '''' Spalte
FROM (SELECT DISTINCT DeineSpalte
FROM DeinTabelle))
loop
if cols is not null then
cols := cols || ',';
end if;
cols := cols || cCols.spalte;
end loop;

--Erstellen des Pivot Select
xQuery := 'SELECT * FROM (SELECT timestamp, Spalte, wert FROM DeinTabelle) PIVOT ( max(wert) FOR Spalte IN (' || Cols || ') ) ORDER BY timestamp';

--Rückgabe über Cursor
OPEN s_cur FOR xQuery;


END;
 
Zurück
Oben