none
Pivotabfrageergebnis weiter auswerten RRS feed

  • Frage

  • Hallo,

    ich habe eine einfache Liste "Verkäufe" mit den Spalten "Produkt" und "Filale":

        Verkäufe   Tabelle: Verkäufe                        

    Ich möchte nun eine Kreuztabelle erstellen, in der die Produkte in den einzelnen Filialen gezählt werden. Dies erreiche ich mit 2 Abfragen:

    SELECT DESTINCT Filale FROM Verkäufe           (Erzeugt wird ein der Filialen "F1,F2,F3").

    und weiter

    SELECT Produkt, strFilialen

    FROM (SELECT Produkt, Filiale

               FROM Verkäufe p)

               PIVOT(

                         count(Filiale),

               FOR Filiale

               IN strFilalen)

               AS ErgTabelle      

    Das klappt soweit auch gut.

    Um feststellen zu können ob alle Produkte auch in allen Filialen verkauft werden und auch wie viel insgesamt, sollen nun aber gleichzeitig noch 2 neue Spalten erzeugt werden, in denen die Anzahl der Filalen für die einzelnen Produkte gezählt werden, sowie die Gesamtzahl für ein Produkt in allen Filialen, und absteigend sortiert nach "Anzahl Filialen" 

    Kann mir da einer auf die Sprünge helfen????

    Donnerstag, 6. September 2012 15:05

Antworten

  • Hallo,

    verknüpfe das die PIVOT Abfrage mit einer zweiten, z. B.:

    CREATE TABLE dbo.Verkäufe
    (
    	Produkt varchar(10),
    	Filiale varchar(10));
    	
    INSERT INTO dbo.Verkäufe VALUES
    	('P1', 'F1'), ('P2', 'F1'), ('P1', 'F2'),
    	('P1', 'F1'), ('P2', 'F2'), ('P1', 'F3');
    GO
    
    SELECT f.Produkt, f.[F1], f.[F2], f.[F3], 
    	g.[Anzahl Filialen], g.[Anzahl Verkäufe]
    FROM (
    	SELECT 
    		Produkt, 
    		Filiale
    	FROM Verkäufe) AS p
    	PIVOT(
    		COUNT(Filiale)
            FOR Filiale IN ([F1], [F2], [F3]))
    	AS f
    JOIN (SELECT 
    		Produkt,
    		COUNT(DISTINCT Filiale) AS [Anzahl Filialen],
    		COUNT(*) AS [Anzahl Verkäufe]
    		FROM Verkäufe
    		GROUP BY Produkt) 
    	AS g 
    		ON f.Produkt = g.Produkt;
    
    /* alternativ:
    CROSS APPLY (SELECT 
    		COUNT(DISTINCT Filiale) AS [Anzahl Filialen],
    		COUNT(*) AS [Anzahl Verkäufe]
    		FROM Verkäufe
    		WHERE f.Produkt = Verkäufe.Produkt) AS g;
    */

    (auskommentiert eine Alternative mit CROSS APPLY, die vermutlich etwas ineffizienter bei größeren Mengen sein dürfte).

    Gruß Elmar

    Donnerstag, 6. September 2012 18:24

Alle Antworten

  • Hallo,

    verknüpfe das die PIVOT Abfrage mit einer zweiten, z. B.:

    CREATE TABLE dbo.Verkäufe
    (
    	Produkt varchar(10),
    	Filiale varchar(10));
    	
    INSERT INTO dbo.Verkäufe VALUES
    	('P1', 'F1'), ('P2', 'F1'), ('P1', 'F2'),
    	('P1', 'F1'), ('P2', 'F2'), ('P1', 'F3');
    GO
    
    SELECT f.Produkt, f.[F1], f.[F2], f.[F3], 
    	g.[Anzahl Filialen], g.[Anzahl Verkäufe]
    FROM (
    	SELECT 
    		Produkt, 
    		Filiale
    	FROM Verkäufe) AS p
    	PIVOT(
    		COUNT(Filiale)
            FOR Filiale IN ([F1], [F2], [F3]))
    	AS f
    JOIN (SELECT 
    		Produkt,
    		COUNT(DISTINCT Filiale) AS [Anzahl Filialen],
    		COUNT(*) AS [Anzahl Verkäufe]
    		FROM Verkäufe
    		GROUP BY Produkt) 
    	AS g 
    		ON f.Produkt = g.Produkt;
    
    /* alternativ:
    CROSS APPLY (SELECT 
    		COUNT(DISTINCT Filiale) AS [Anzahl Filialen],
    		COUNT(*) AS [Anzahl Verkäufe]
    		FROM Verkäufe
    		WHERE f.Produkt = Verkäufe.Produkt) AS g;
    */

    (auskommentiert eine Alternative mit CROSS APPLY, die vermutlich etwas ineffizienter bei größeren Mengen sein dürfte).

    Gruß Elmar

    Donnerstag, 6. September 2012 18:24
  • Hallo Elmar,

    erst einmal vielen Dank für die schnelle Antwort. Das mit der angehängten Abfrage macht Sinn und ist i.m.A. auch logisch nachvollziehbar.

    Ich würde dein Vorschlag am liebsten sofort testen, aber Morgen ist Betriebsfest, dann das WE, aber am Montag gehe ich sofort daran.

    Da die Spaltenwerte von "Filiale" vorher nicht bekannt sind, brauche ich eine dynamische Abfrage, aber ich glaube das bekomme ich mit deinem Vorschlag auch gebastelt.

    Die Tabelle "Verkäufe" wird u.U. mehrere Mio Datensätze beinhalten, deshalb werde ich wohl CROSS APPLY aus Performancegründen verwerfen können, oder? (Leider bin ich noch Anfänger, sodass mir die Syntax von Cross Apply auch nicht geläufig ist).

    Auf jeden Fall freue ich mich, den Vorschlag ausprobieren zu dürfen. :-) Ich werde über das Ergebnis sofort nachberichten!

    Nur zur Kenntnis nebenbei:

    Die Abfragen starte ich im Moment noch aus einem EXCEL-VBA-Makro, die Ergebnisse der Abfragen werden in ein ADO.Recordset gespeichert, welches ich mit VBA dann auf ein neu generiertes Excel-Tabellenblatt schreibe. Natürlich könnte ich die gejointe Abfrage auch mit EXCEL-Funktionen lösen. Das will ich aber nicht, da ich parallel noch an der eigenständigen Anwendung unter Visual Studio arbeite und da nützen mir die EXCEL-Funktionen nichts.

    Schönen Gruß und schönes WE

    Jürgen

    Donnerstag, 6. September 2012 19:21
  • Hallo Emar,

    dein Vorschlag klappt wunderbar. Das ist genau das, was ich wollte.

    Recht Herzlichen Dank.

    Gruß Jürgen

    Sonntag, 9. September 2012 08:36
  • HalliHallo,

    ich muss dieses Thread nochmal aufgreifen. Leider ergibt ein neues Problem, wenn die Produkte in mehreren Spalten auftauchen, z.B. in sog. Produktpaketen:

    CREATE TABLE dbo.Verkäufe
    (
     ProduktPaket1 varchar(10),
     ProduktPaket2 varchar(10),
     Filiale varchar(10));
     
    INSERT INTO dbo.Verkäufe VALUES
     ('P1', 'P2', 'F1'), ('P2', 'P2','F1'), ('P1', 'P1', 'F2'),
     ('P1', 'P2', 'F1'), ('P2', 'P2','F2'), ('P1', 'P2', 'F3'),
     ('P1', 'P3', 'F3'), ('P2', 'P3','F2'), ('P3', 'P2', 'F3');
    GO

    Weiß hier jemand Rat???

    Gruß Jürgen

    Dienstag, 27. Dezember 2016 16:14
  • Hallo Jürgen,

    wenn Du eine andere/neue Frage hast, starte bitte einen neuen Thread statt auf einen alten, von jemand anderen gestarteten Thread zu antworten.

    Dann fehlt in Deinem Post auch noch, worin das Problem liegt und welches Ergebnis Du erwartest.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Dienstag, 27. Dezember 2016 18:02
  • Hallo Olaf,

    der Threadstarter war ich damals selbst :-) und hatte den Nick "Farmex". Ich dachte, dass ich den Thread wieder aufnehmen kann, da die Änderungen auf der Grundfrage basieren.

    Das erwartete Ergebnis ist:

    Produkt F1 F2 F3 Anzahl Filialen Anzahl Verkäufe
    P1 2 2 2 3 6
    P2 4 2 2 3 8
    P3 1 1 2 3 4

    Gruß Jürgen

    Mittwoch, 28. Dezember 2016 08:38