Dynamische Spaltennamen

[ThA]

Hallo zusammen

Ich arbeite mit PL/SQL Developer 7.1 und Oracle 11g.

Ich habe eine Tabelle mit Artikeln und deren Monats-Umsätze:
ARNR MON01 MON02 MON03 MON04 MON05 ...

Ich möchte jetzt auf die Daten der letzen drei Monate zurückgreifen.
Beispiel:
Select arnr, mon06, mon05, mon04

Frage: Wie mache ich das dynamisch?
Ansatz: select arnr, MON(to_char(sysdate-310,'MM')),MON(to_char(sysdate-310,'MM')-1),MON(to_char(sysdate-310,'MM')-2),

Danke und Gruss, Thomas

PS: Mein nächstes Problem wird der Jahreswechsel sein :-)

[fanderlf]

Schau mal:

http://psoug.org/reference/date_func.html

Wenn Du direkt mit dem Date objekt arbeitest kannst du sowas wie AddMonths machen. Dort kannst Du auch negative Zahlen eingeben um Monate abzuziehen. Damit lässt sich Deine Funktionalität doch bestimmt bauen

Achso... man sollte natürlich vernünftig lesen... die Spaltennamen kannst Du so nicht dynamisch machen. SQL ist per se statisch. Da musst Du wohl auf PL/SQL mit dynamischem SQL gehen, wenn Du so etwas brauchst.
Reicht es denn nicht auch die Daten der letzten 3 Monate zu filtern und dann darüber abfragen zu machen?

Es gibt durchaus noch andere Varianten wie man das machen könnte. Vielleicht könntest Du mal weiter erklären was hinterher mit den Daten passieren wird.

[ThA]

Danke für den Link. Interessant!

Meine Aufgabe. Ich muss für einen Kunden den Artikel-Umsatz der letzten 12 Monat (NICHT Kalenderjahr) zeigen - als Summe und einzeln.

Die Daten liegen wie folgt vor:

ARTIKEL JAHR UM01 UM02 UM03……UM11 UM12
artikel1 2011 99 822 547 …… … 100 200
artikel1 2012 100
...


Die neue Auswertung soll wie folgt sein:
Summe der letzten 12 Monat und die x-mal die Monatszahlen. Im Beispiel unten die letzten 3 Einzelmonate

Artikelnummer / Summe der letzten 12 Monate / lfdMonat / vorMonat / vorvorMonat
[FONT="Courier New"]
ARTIKEL SUMME12 Monat Monat-1 Monat-2
artikel1 400 100 200 100

Ist das verständlich?

Gruss, Thomas

[jum]

Wo klemmt es dann eigentlich ?
Da Du das nicht preisgibst, hier mal ein "unkonventioneller" Denkansatz:

SET SERVEROUTPUT ON SIZE 900000;

CREATE TABLE test_tab1 AS
  (SELECT 'artikel1' artikel, 2011 jahr, 99 um01, 822 um02, 547 um03, 123 um04, 546 um05, 887 um06,
                                         19 um07, 422 um08, 345 um09, 123 um10, 592 um11, 006 um12 FROM dual UNION ALL
   SELECT 'artikel1'        , 2012     , 54     , 845     , 377     , 076     , 678     , 300     ,
                                         87     , 422     , 345     , 123     , 592     , 781      FROM dual);

VARIABLE mon0 VARCHAR2(4) 

VARIABLE mon1 VARCHAR2(4) 

VARIABLE mon2 VARCHAR2(4) 

VARIABLE g_ref refcursor  

exec :mon0 := 'um' || to_char(extract(MONTH FROM sysdate)  ,'FM09');

exec :mon1 := 'um' || to_char(extract(MONTH FROM sysdate)+1,'FM09');

exec :mon2 := 'um' || to_char(extract(MONTH FROM sysdate)+2,'FM09');

BEGIN 
  OPEN :g_ref FOR 'SELECT artikel, jahr, '||:mon0||', '||:mon1||', '||:mon2||' FROM test_tab1';
END;                                         

print g_ref  

ARTIKEL        JAHR       UM01       UM02       UM03
-------- ---------- ---------- ---------- ----------
artikel1       2011         99        822        547
artikel1       2012         54        845        377

2 rows selected.

[defo]

[[[Also gemäß Deiner Anforderung sehe ich das Problem, dass die letzten 12 Monate nur schwer mit dem dargestellten Wunschergebnis zusammen passen.
Darin schreibst Du in die 2. Spalte das Jahr. Was soll dann bei der Betrachtung der letzten 12 Monate im Juli 2011 rauskommen?]]]
>>> Der Teil ist Schwachsinn, ich hab mich verlesen.

Den Ansatz von jum finde ich interessant, aber da fehlt irgendwie das 12 Monate rückwärts und das müsste ja dann noch jeweils auf 2 Datensätze der Ursprungsdaten abgebildet werden.

Technisch hast Du hier eine klassische Kreuztabelle als Ausgangsbasis. Das ist leider kein optimaler Ausgangspunkt. Die Originaldaten müsstest Du also transformieren.

Das geht seit Oracle 11 allerdings halbwegs bequem, leider nicht ganz dynamisch.
Schau mal, ob Dir die SQL -Operatoren PIVOT und UNPIVOT weiterhelfen..

Beginnen würde ich mit UNPIVOT, damit kannst Du Deine Kreuztabelle wieder in eine normale Liste umbauen. Das Ergebnis, also diese Liste, baust Du mit PIVOT wieder zu einer Kreuztabelle um, diesmal gemäß der neuen Anforderungen, alles per SQL ohne SP.

[defo]

Ich hab mal die Tabelle von jum geklaut und angelegt:

[highlight=sql]
SQL> CREATE TABLE test_tab1 AS
2 (SELECT 'artikel1' artikel, 2011 jahr,
3 99 um01, 822 um02, 547 um03, 123 um04, 546 um05, 887 um06,
4 19 um07, 422 um08, 345 um09, 123 um10, 592 um11, 006 um12 FROM dual UNION ALL
5 SELECT 'artikel1' , 2012,
6 54 , 845 , 377 , 076 , 678 , 300 ,
7 87 , 422 , 345 , 123 , 592 , 781 FROM dual);

Tabelle wurde erstellt.

SQL>
[/highlight]

Dann kommt als erster Teil der Transformation das Unpivot:
[highlight=sql]
SQL>
SQL> CREATE OR REPLACE VIEW VTestTabUnPivot AS
2 SELECT * FROM test_tab1
3 UNPIVOT (anzahl FOR Monat IN (
4 um01 AS 1, um02 AS 2, um03 AS 3, um04 AS 4, um05 AS 5, um06 AS 6,
5 um07 AS 7, um08 AS 8, um09 AS 9, um10 AS 10, um11 AS 11, um12 AS 12));

View wurde erstellt.

SQL>
SQL> select * from VTestTabUnPivot;

ARTIKEL JAHR MONAT ANZAHL
-------- ---------- ---------- ----------
artikel1 2011 1 99
artikel1 2011 2 822
artikel1 2011 3 547
artikel1 2011 4 123
artikel1 2011 5 546
artikel1 2011 6 887
artikel1 2011 7 19
artikel1 2011 8 422
artikel1 2011 9 345
artikel1 2011 10 123
artikel1 2011 11 592

ARTIKEL JAHR MONAT ANZAHL
-------- ---------- ---------- ----------
artikel1 2011 12 6
artikel1 2012 1 54
artikel1 2012 2 845
artikel1 2012 3 377
artikel1 2012 4 76
artikel1 2012 5 678
artikel1 2012 6 300
artikel1 2012 7 87
artikel1 2012 8 422
artikel1 2012 9 345
artikel1 2012 10 123

ARTIKEL JAHR MONAT ANZAHL
-------- ---------- ---------- ----------
artikel1 2012 11 592
artikel1 2012 12 781

24 Zeilen ausgewõhlt.

SQL>
[/highlight]

Hierbei finde ich spannend, ob Du tatsächlich nur diese aufbereiteten Kreuztabellendaten hast oder auch die Basisdaten in Listenform. Dann könntest Du Dir das unpivot natürlich sparen. Es sei denn, die Aufbereitung/Erstellen hat soviel gekostet, dass die Transformation der Ergebnisdaten erstrebenswerter ist.

2.Teil der Transformation (zurück)
Das folgende Statement macht 2 Dinge, es filtert die Daten auf "letzte 12" Monate und pivotisiert es wieder.
Die Summe hab ich nicht drin, aber das kann man ja recht einfach anbauen.
Vielleicht musst Du noch an den Offsets sysdate[-365] drehen.

[highlight=sql]
SQL> SELECT *
2 FROM (SELECT artikel, anzahl, rownum as last12 from
3 (SELECT artikel, Monat, anzahl,
4 to_date(jahr || trim(to_char(monat, '00')) || '01', 'YYYYMMDD')
5 as Datum
6 FROM VTestTabUnPivot
7 order by 4) x
8 where Datum >sysdate -365
9 and Datum <sysdate
10 order by 3 desc)
11 PIVOT (SUM(anzahl) AS M
12 FOR (Last12)
13 IN (12 AS "MO-1", 11 AS "MO-2", 10 AS "MO-3", 9 AS "MO-4", 8 AS "MO-5", 7 AS "MO-6",
14 6 AS "MO-7", 5 AS "MO-8", 4 AS "MO-9", 3 AS "MO-10", 2 AS "MO-11", 1 AS "MO-12"));

ARTIKEL MO-1_M MO-2_M MO-3_M MO-4_M MO-5_M MO-6_M MO-7_M MO-8_M MO-9_M MO-10_M MO-11_M MO-12_M
-------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
artikel1 54 6 592 123 345 422 19 887 546 123 547 822

SQL>

[/highlight]

Dynamische Spalten gibts so allerdings keine, nur dynamische Inhalte.

[defo]

Ach nochwas, die Minus-Schreibweise in den Ergebnisspalten habe ich nur zum Verständnis (nach Vorgabe) gemacht. Ist aber heikel, mit den Anführungszeichen. Real würde ich es nicht machen, wenn es weiterverwendet wird.

[ThA]

Besten Dank für eure Hilfe.

defo, deinen Vorschlage werde ich mal weiterverfolgen. Ich muss/darf aber anfügen, dass ich keine sooo tiefen Kentnisse von SQL habe.

Ich versuche den Code zu verstehen und mit realen Daten nachzuvollziehen.

Besten Dank und ein schönes Wochenende.
Thomas

[defo]

Das Beispiel ist so wie es dort steht lauffähig. Du musst eigentlich nur Deine Tabellen- und Feldnamen einsetzen. Nagut, die Summe fehlt noch.
Ich drück Dir die Daumen! Wenn es noch Probleme gibt: Je konkreter die Frage, desto leichter findest Du hier Hilfe.

[ThA]

Ich konnte dein Beispiel nachvollziehen - auch mit eigenen Daten aus einer Statistiktabelle. ich habe die Daten auch in eine neue Tabelle zurückgeschrieben.

Die Jahressumme hänge ich später an, weil noch nicht klar ist, ob die Summen des Kalenderjahres oder die Summe der letzten 12 Monate erforderlich sind.

Das Datum kann ich auch dynamisch eingegeben - falls erforderlich.

Wenn ich die Tabelle test_tab1 um weitere Artikel ergänze (weitere UNION für Artikel2), stosse ich auf das Problem, dass die letzeten 12 Monate für alle Artikel, nicht nur für einen ausgewält werden.

Die LAST12 werden dann:
artikel1 19 12
artikel2 87 11
artikel1 887 10
artikel2 300 9
artikel1 546 8
artikel2 678 7
artikel2 76 6
artikel1 123 5
artikel2 377 4
artikel1 547 3
artikel1 822 2
artikel2 845 1

Und die Ausgabe bzw. Endtabelle:
ARTIKEL MO-1_M MO-2_M MO-3_M MO-4_M MO-5_M MO-6_M MO-7artikel2 87 300 678 76
artikel1 19 887 546

Ich bin nun am Hirnen, wie ich dein Script auf mehrere Artikel erweitern kann.

Gruss, Thomas

[defo]

Ich wäre jetzt nicht auf die Idee gekommen, dass nur ein Artikel angezeigt werden soll.
Den müsstest Du genau wie das Datum im inneren Select des 2. Teils rausfiltern.

Poste doch einfach Dein Statement, dass muss man nicht immer rumfantasieren.

[ThA]

Hallo defo

Hier mein Test-Code aus deiner Vorlage:

-- Testdaten bereitstellen: LIVE-Daten mit SELECT auf die Artikelstatistik Tabelle T158ARST
drop table test_tab2;
CREATE TABLE test_tab2 AS  
(SELECT 'artikel1' artikel, 2011 jahr, 
   99 um01, 822 um02, 547 um03, 123 um04, 546 um05, 887 um06,
   19 um07, 422 um08, 345 um09, 123 um10, 592 um11, 006 um12 FROM dual 
 UNION ALL
 SELECT 'artikel1'        , 2012, 
         54     , 845     , 377     , 076     , 678     , 300     ,
         87     , 422     , 345     , 123     , 592     , 781      FROM dual
 union all
 SELECT 'artikel2'        , 2011, 
         54     , 845     , 377     , 076     , 678     , 300     ,
         87     , 422     , 345     , 123     , 592     , 781      FROM dual
 union all
 SELECT 'artikel2'        , 2012, 
         54     , 845     , 377     , 076     , 678     , 300     ,
         87     , 422     , 345     , 123     , 592     , 781      FROM dual);

select * from test_tab2;

drop view VTestTabUnPivot;
CREATE OR REPLACE VIEW VTestTabUnPivot AS
  SELECT * FROM   test_tab2
  UNPIVOT (anzahl FOR Monat IN (um01 AS 1, um02 AS 2, um03 AS 3, um04 AS 4,  um05 AS 5,  um06 AS 6,
                                um07 AS 7, um08 AS 8, um09 AS 9, um10 AS 10, um11 AS 11, um12 AS 12)); 
                                
select * from VTestTabUnPivot;

SELECT *  
  FROM (SELECT artikel, anzahl, rownum AS last12 
          FROM (SELECT artikel, Monat, anzahl,
                       to_date(jahr || trim(to_char(monat, '00')) || '01', 'YYYYMMDD') AS Datum
                  FROM VTestTabUnPivot  
                 ORDER BY 4) x 
         WHERE Datum >sysdate -365
           AND Datum <sysdate 
           --and artikel = 'artikel1'
         ORDER BY 3 DESC)
 PIVOT (SUM(anzahl) AS M
   FOR (Last12) 
    IN (12 AS "MO-1", 11 AS "MO-2", 10 AS "MO-3", 9 AS "MO-4",  8 AS "MO-5",  7 AS "MO-6",
         6 AS "MO-7",  5 AS "MO-8",  4 AS "MO-9", 3 AS "MO-10", 2 AS "MO-11", 1 AS "MO-12"));

Die Daten würde ich dann in eine neue Tabelle stellen (Auszug aus der LIVE-Abfrage, Datum dynamisch):

insert into test_tab10
(
SELECT *  
  FROM (SELECT arnr, anzahl, rownum AS last12 FROM 
          (SELECT arnr, Monat, anzahl,
                  to_date(jahr || trim(to_char(monat, '00')) || '01', 'YYYYMMDD') AS Datum
             FROM VTestTabUnPivot 
 --           where arnr = '100400' 
            ORDER BY 4) x 
          WHERE Datum >to_date('&_sysdate') -365   -- variabel für Test mit anderen Monaten
            AND Datum <to_date('&_sysdate')  
          ORDER BY 3 DESC)
 PIVOT (SUM(anzahl) AS M
   FOR (Last12) 
    IN (12 AS "MO-1", 11 AS "MO-2", 10 AS "MO-3", 9 AS "MO-4",  8 AS "MO-5",  7 AS "MO-6",
         6 AS "MO-7",  5 AS "MO-8",  4 AS "MO-9", 3 AS "MO-10", 2 AS "MO-11", 1 AS "MO-12")) 
);

[defo]

Unterscheidet sich da irgendwas außer den Tabellennamen?
Was ist genau das Problem ? Was willst Du erreichen?

[ThA]

Die LIVE-Abfrage ist in etwas dassselbe: INSERT-Anweisung, zum Teil andere Tabellennamen und Datum zum Eingeben.

Ich möchte erreichen, dass in der tabelle Test_Tab10 alle Artikel mit dem Umsatz der letzten 12 Monate stehen.

Diese Tabelle möche ich nachts aufbereiten und in anderen Abfrage verwenden.

Gruss, Thomas

[defo]

Da hab ich wohl geschlampt.
Nimm das innere Select aus dem Pivotstatement und schau Dir den Output an:
[highlight=sql]
SQL> SELECT artikel, anzahl, rownum AS last12 FROM
2 (SELECT artikel, Monat, anzahl,
3 to_date(jahr || trim(to_char(monat, '00')) || '01', 'YYYYMMDD')
4 AS Datum
5 FROM VTestTabUnPivot
6 ORDER BY 4) x
7 WHERE Datum >sysdate -365
8 AND Datum <sysdate
9 ORDER BY 3 DESC
10 ;

ARTIKEL ANZAHL LAST12
-------- ---------- ----------
artikel1 54 24
artikel2 54 23
artikel2 781 22
artikel1 6 21
artikel2 592 20
artikel1 592 19
artikel2 123 18
artikel1 123 17
artikel2 345 16
artikel1 345 15
artikel2 422 14
artikel1 422 13
artikel1 19 12
artikel2 87 11
artikel1 887 10
artikel2 300 9
artikel1 546 8
artikel2 678 7
artikel2 76 6
artikel1 123 5

ARTIKEL ANZAHL LAST12
-------- ---------- ----------
artikel2 377 4
artikel1 547 3
artikel1 822 2
artikel2 845 1

24 rows selected

SQL>
[/highlight]

Was geht schief?
Die Werte aus Spalte "Last12" sollen eigentlich die "Vorlage" für Deine neuen Spalten sein. Per Definition / Vorgabe liegen sie zwischen 12 und 1.
Das musst Du ohne Artikeleinschränkung hinkriegen. Das ist der Kern dieser Abfrage. Die Rownum Funktion ist demnach ein Griff ins Klo gewesen. Die Werte müssen unabhängig von der Artikelanzahl auf 12 bis 1 transformiert werden.
[highlight=sql]
SQL> SELECT artikel, anzahl, /*datum,*/
2 --Differenzwert bilden aus jetzt-Datumswert als Ersatz für rownum
3 abs(months_between(to_date(to_char(sysdate-365,'YYYYMM')||'01','YYYYMMDD'),datum)) as last12
4 FROM (SELECT artikel, Monat, anzahl,
5 to_date(jahr || trim(to_char(monat, '00')) || '01', 'YYYYMMDD') AS Datum
6 FROM VTestTabUnPivot
7 ORDER BY 4) x
8 WHERE Datum >sysdate -365
9 AND Datum <sysdate
10 --and artikel = 'artikel1'
11 ORDER BY 1,3 DESC
12 ;

ARTIKEL ANZAHL LAST12
-------- ---------- ----------
artikel1 54 12
artikel1 6 11
artikel1 592 10
artikel1 123 9
artikel1 345 8
artikel1 422 7
artikel1 19 6
artikel1 887 5
artikel1 546 4
artikel1 123 3
artikel1 547 2
artikel1 822 1
artikel2 54 12
artikel2 781 11
artikel2 592 10
artikel2 123 9
artikel2 345 8
artikel2 422 7
artikel2 87 6
artikel2 300 5

ARTIKEL ANZAHL LAST12
-------- ---------- ----------
artikel2 678 4
artikel2 76 3
artikel2 377 2
artikel2 845 1

24 rows selected

SQL>
[/highlight]
Wenn Du das wieder in das alte PivotStatement einsetzt, sieht es besser aus. Ohne Rownum ist auch die Sortierung unnötig.
(wer weiß, vielleicht hab ich noch was übersehen)
[highlight=sql]
SQL> SELECT *
2 FROM (SELECT artikel, anzahl,
3 --Differenzwert bilden aus jetzt-Datumswert als Ersatz für rownum
4 abs(months_between(to_date(to_char(sysdate-365,'YYYYMM')||'01','YYYYMMDD'),datum)) as last12
5 FROM (SELECT artikel, Monat, anzahl,
6 to_date(jahr || trim(to_char(monat, '00')) || '01', 'YYYYMMDD') AS Datum
7 FROM VTestTabUnPivot) x
8 WHERE Datum >sysdate -365
9 AND Datum <sysdate --and artikel = 'artikel1'
10 )
11 PIVOT (SUM(anzahl) AS M
12 FOR (Last12)
13 IN (12 AS "MO-1", 11 AS "MO-2", 10 AS "MO-3", 9 AS "MO-4", 8 AS "MO-5", 7 AS "MO-6",
14 6 AS "MO-7", 5 AS "MO-8", 4 AS "MO-9", 3 AS "MO-10", 2 AS "MO-11", 1 AS "MO-12"));

ARTIKEL MO-1_M MO-2_M MO-3_M MO-4_M MO-5_M MO-6_M MO-7_M MO-8_M MO-9_M MO-10_M MO-11_M MO-12_M
-------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
artikel2 54 781 592 123 345 422 87 300 678 76 377 845
artikel1 54 6 592 123 345 422 19 887 546 123 547 822

[/highlight]

Und noch was:

WHERE Datum >to_date('&_sysdate') -365 -- variabel für Test mit anderen Monaten

to_date ohne Formatmaske ist ab und zu tödlich,
to_date(<date-text>,<date-maske>) ist empfehlenswert.