Remove From My Forums

Wie fasst man Spalten einer Tabelle zusammen und zählt das Vorkommen der einzelnen Werte in den einzelnen Spalten?

Frage

Anmelden

Hallo zusammen,

ich habe folgende Tabelle:

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', 'P4', 'F3');
GO

ProduktPaket1	ProduktPaket2	Filiale
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	P4	F3

Als Ergebnistabelle möchte ich folgende Ausgabe erreichen:

Produkt	F1	F2	F3	Anzahl Filialen	Anzahl Verkäufe	ProduktPaket1	ProduktPaket2
P1	2	2	2	3	6	5	1
P2	4	2	2	3	8	3	5
P3	0	1	2	2	3	1	2
P4	0	0	1	1	1	0	1

Ich benutze folgende dynamische Abfrage:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Filiale)
	  FROM (SELECT DISTINCT Filiale FROM Verkäufe) AS Filialen

SET @DynamicPivotQuery = 
N'SELECT f.[ProduktPaket1], ' + STUFF(@ColumnName, 1, 0, '') + ', g.[Anzahl Filialen], g.[Anzahl Verkäufe] 
		FROM (SELECT [ProduktPaket1], [ProduktPaket2], [Filiale]
					  FROM Verkäufe
			  ) AS p

PIVOT ( COUNT([Filiale])
		FOR [Filiale] IN (' + Stuff(REPLACE(@ColumnName, ', f.[', ',['), 1, 0, '') + ')
	  ) AS f 

JOIN (SELECT [ProduktPaket1], COUNT(DISTINCT [Filiale]) AS [Anzahl Filialen], COUNT (*) AS [Anzahl Verkäufe]
			FROM Verkäufe
			GROUP BY [ProduktPaket1]
	 )
AS g ON (f.[ProduktPaket1] = g.[ProduktPaket1] AND g.[Anzahl Filialen]>= 1)
ORDER BY g.[Anzahl Filialen] DESC, g.[Anzahl Verkäufe] DESC';

--PRINT @DynamicPivotQuery
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

Leider ergibt sich folgende Tabelle:

Weiß hier jemand Rat???

Gruß Jürgen

Mittwoch, 28. Dezember 2016 14:20

Antworten

Anmelden

Hallo Jürgen,

das hatte ich mir einfacher vorgestellt; aber wenn man erst einmal damit angefangen ist packt einen ja doch der Ehrgeiz.

Ich hoffe, es passt jetzt alles.

Schönen Abend.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Filiale)
	  FROM (SELECT DISTINCT Filiale FROM Verkäufe) AS Filialen

SET @DynamicPivotQuery = 
N'SELECT f.[Produkt] AS Produkt, ' + STUFF(@ColumnName, 1, 0, '') + ', [Anzahl Filialen], [Anzahl Verkäufe], [Produktpaket1], [Produktpaket2]
	FROM (SELECT [Produkt], [Filiale]
					FROM (
						SELECT ProduktPaket1 AS Produkt, Filiale, 1 AS Produktpaket
						FROM Verkäufe
						UNION ALL
						SELECT ProduktPaket2 AS Produkt, Filiale, 2 AS Produktpaket
						FROM Verkäufe
						) AS x
		 ) AS p

PIVOT ( COUNT([Filiale])
		FOR [Filiale] IN (' + Stuff(REPLACE(@ColumnName, ', f.[', ',['), 1, 0, '') + ')
	  ) AS f 

LEFT JOIN (
		SELECT COUNT (ISNULL (filiale, 0)) AS [Anzahl Verkäufe], Produkt
		FROM
		(
			SELECT ProduktPaket1 AS Produkt, Filiale, 1 AS Produktpaket
			FROM Verkäufe
			UNION ALL
			SELECT ProduktPaket2 AS Produkt, Filiale, 2 AS Produktpaket
			FROM Verkäufe
		) AS x 
		GROUP BY Produkt

) AS z ON z.Produkt = f.produkt

LEFT JOIN (
		SELECT COUNT (ISNULL (filiale, 0)) AS [Produktpaket1], Produkt
		FROM
		(
			SELECT ProduktPaket1 AS Produkt, Filiale, 1 AS Produktpaket
			FROM Verkäufe
			UNION ALL
			SELECT ProduktPaket2 AS Produkt, Filiale, 2 AS Produktpaket
			FROM Verkäufe
		) AS r 
		WHERE Produktpaket = 1
		GROUP BY Produkt

) AS s ON (s.Produkt = z.Produkt) 

LEFT JOIN (
		SELECT COUNT (ISNULL (filiale, 0)) AS [Produktpaket2], Produkt
		FROM
		(
			SELECT ProduktPaket1 AS Produkt, Filiale, 1 AS Produktpaket
			FROM Verkäufe
			UNION ALL
			SELECT ProduktPaket2 AS Produkt, Filiale, 2 AS Produktpaket
			FROM Verkäufe
		) AS w 
		WHERE Produktpaket = 2
		GROUP BY Produkt

) AS k on k.Produkt = z.Produkt 

LEFT JOIN (
		SELECT MAX (ISNULL (ROW, 0)) AS [Anzahl Filialen],  Produkt
		FROM
		(
		SELECT Filiale, Produkt, ROW_NUMBER() OVER(PARTITION BY Produkt ORDER BY filiale ASC) AS [ROW]
		FROM
		(
			SELECT ProduktPaket1 AS Produkt, Filiale, 1 AS Produktpaket
			FROM Verkäufe
			UNION ALL
			SELECT ProduktPaket2 AS Produkt, Filiale, 2 AS Produktpaket
			FROM Verkäufe
		) AS x 
		GROUP BY Produkt, filiale
		) AS v
		GROUP BY Produkt

) AS e on e.Produkt = z.Produkt 

';

--PRINT @DynamicPivotQuery
--Execute the Dynamic Pivot Query

EXEC sp_executesql  @DynamicPivotQuery

Als Antwort markiert Jürgen Sch Freitag, 30. Dezember 2016 09:42

Donnerstag, 29. Dezember 2016 18:12

Alle Antworten

Anmelden

Nachtrag:

Sorry, hatte den falschen SQL kopiert. Hier der neue SQL:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Filiale)
	  FROM (SELECT DISTINCT Filiale FROM Verkäufe) AS Filialen

SET @DynamicPivotQuery = 
N'SELECT f.[ProduktPaket1] AS Produkt, ' + STUFF(@ColumnName, 1, 0, '') + ', 
		 g.[Anzahl Filialen], 
		 g.[Anzahl Verkäufe], 
		 g.[Anzahl P1], 
		 g.[Anzahl P2] 
	FROM (SELECT [ProduktPaket1], [Filiale]
					FROM Verkäufe
		 ) AS p

PIVOT ( COUNT([Filiale])
		FOR [Filiale] IN (' + Stuff(REPLACE(@ColumnName, ', f.[', ',['), 1, 0, '') + ')
	  ) AS f 

JOIN (SELECT [ProduktPaket1], 
			COUNT(DISTINCT [Filiale]) AS [Anzahl Filialen], 
			COUNT (*) AS [Anzahl Verkäufe],
			COUNT(DISTINCT [ProduktPaket1]) as [Anzahl P1],
			COUNT(DISTINCT [ProduktPaket2]) as [Anzahl P2]
			FROM Verkäufe
			GROUP BY [ProduktPaket1]
	 ) AS g

ON (f.[ProduktPaket1] = g.[ProduktPaket1] AND g.[Anzahl Filialen]>= 1)
ORDER BY g.[Anzahl Filialen] DESC, g.[Anzahl Verkäufe] DESC';

--PRINT @DynamicPivotQuery
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

Und hier das Ergebnis:

Produkt P4 wird gar nicht erkannt und die Anzahl in den Filialen und den Produktpaketen passt nicht!!

:-(

Gruß Jürgen

Mittwoch, 28. Dezember 2016 14:38

Anmelden

Hallo Jürgen,

das Problem ist, dass Du in Deiner Pivot nur ProduktPaket1 abfragst - und nicht auch ProduktPaket2.

In Produktpaket2 ist auch das Produkt P4 enthalten, und deshalb hast Du keinen Treffer.

Ich habe die Kernabfrage deshalb mit einem UNION erweitert, der sowohl ProduktPaket1 als auch Produktpaket2 beinhaltet. Allerdings sind die Fililalen nun statistisch - SUM (F1), SUM (F2), ...

Wie man das nun wieder dynamisch gestalten kann, dazu fehlt mir leider aktuell die Idee; auch für die weiteren Spalten.

Aber ist es denn bis hier erst einmal korrekt vom Ergebnis?

Schöne Grüße

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Filiale)
	  FROM (SELECT DISTINCT Filiale FROM Verkäufe) AS Filialen

SET @DynamicPivotQuery = 
N'SELECT Produkt, SUM (F1) AS F1, SUM (F2) AS F2, SUM (F3) AS F3
FROM
(
SELECT f.[ProduktPaket1] AS Produkt, ' + STUFF(@ColumnName, 1, 0, '') + '
	FROM (SELECT [ProduktPaket1], [Filiale]
					FROM Verkäufe
		 ) AS p

PIVOT ( COUNT([Filiale])
		FOR [Filiale] IN (' + Stuff(REPLACE(@ColumnName, ', f.[', ',['), 1, 0, '') + ')
	  ) AS f 

UNION

SELECT f.[ProduktPaket2] AS Produkt, ' + STUFF(@ColumnName, 1, 0, '') + '
	FROM (SELECT [ProduktPaket2], [Filiale]
					FROM Verkäufe
		 ) AS p

PIVOT ( COUNT([Filiale])
		FOR [Filiale] IN (' + Stuff(REPLACE(@ColumnName, ', f.[', ',['), 1, 0, '') + ')
	  ) AS f 

) AS t1
GROUP BY Produkt';

PRINT @DynamicPivotQuery
--Execute the Dynamic Pivot Query

EXEC sp_executesql  @DynamicPivotQuery

Donnerstag, 29. Dezember 2016 16:11

Anmelden

Hallo Jürgen,

das hatte ich mir einfacher vorgestellt; aber wenn man erst einmal damit angefangen ist packt einen ja doch der Ehrgeiz.

Ich hoffe, es passt jetzt alles.

Schönen Abend.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Filiale)
	  FROM (SELECT DISTINCT Filiale FROM Verkäufe) AS Filialen

SET @DynamicPivotQuery = 
N'SELECT f.[Produkt] AS Produkt, ' + STUFF(@ColumnName, 1, 0, '') + ', [Anzahl Filialen], [Anzahl Verkäufe], [Produktpaket1], [Produktpaket2]
	FROM (SELECT [Produkt], [Filiale]
					FROM (
						SELECT ProduktPaket1 AS Produkt, Filiale, 1 AS Produktpaket
						FROM Verkäufe
						UNION ALL
						SELECT ProduktPaket2 AS Produkt, Filiale, 2 AS Produktpaket
						FROM Verkäufe
						) AS x
		 ) AS p

PIVOT ( COUNT([Filiale])
		FOR [Filiale] IN (' + Stuff(REPLACE(@ColumnName, ', f.[', ',['), 1, 0, '') + ')
	  ) AS f 

LEFT JOIN (
		SELECT COUNT (ISNULL (filiale, 0)) AS [Anzahl Verkäufe], Produkt
		FROM
		(
			SELECT ProduktPaket1 AS Produkt, Filiale, 1 AS Produktpaket
			FROM Verkäufe
			UNION ALL
			SELECT ProduktPaket2 AS Produkt, Filiale, 2 AS Produktpaket
			FROM Verkäufe
		) AS x 
		GROUP BY Produkt

) AS z ON z.Produkt = f.produkt

LEFT JOIN (
		SELECT COUNT (ISNULL (filiale, 0)) AS [Produktpaket1], Produkt
		FROM
		(
			SELECT ProduktPaket1 AS Produkt, Filiale, 1 AS Produktpaket
			FROM Verkäufe
			UNION ALL
			SELECT ProduktPaket2 AS Produkt, Filiale, 2 AS Produktpaket
			FROM Verkäufe
		) AS r 
		WHERE Produktpaket = 1
		GROUP BY Produkt

) AS s ON (s.Produkt = z.Produkt) 

LEFT JOIN (
		SELECT COUNT (ISNULL (filiale, 0)) AS [Produktpaket2], Produkt
		FROM
		(
			SELECT ProduktPaket1 AS Produkt, Filiale, 1 AS Produktpaket
			FROM Verkäufe
			UNION ALL
			SELECT ProduktPaket2 AS Produkt, Filiale, 2 AS Produktpaket
			FROM Verkäufe
		) AS w 
		WHERE Produktpaket = 2
		GROUP BY Produkt

) AS k on k.Produkt = z.Produkt 

LEFT JOIN (
		SELECT MAX (ISNULL (ROW, 0)) AS [Anzahl Filialen],  Produkt
		FROM
		(
		SELECT Filiale, Produkt, ROW_NUMBER() OVER(PARTITION BY Produkt ORDER BY filiale ASC) AS [ROW]
		FROM
		(
			SELECT ProduktPaket1 AS Produkt, Filiale, 1 AS Produktpaket
			FROM Verkäufe
			UNION ALL
			SELECT ProduktPaket2 AS Produkt, Filiale, 2 AS Produktpaket
			FROM Verkäufe
		) AS x 
		GROUP BY Produkt, filiale
		) AS v
		GROUP BY Produkt

) AS e on e.Produkt = z.Produkt 

';

--PRINT @DynamicPivotQuery
--Execute the Dynamic Pivot Query

EXEC sp_executesql  @DynamicPivotQuery

Als Antwort markiert Jürgen Sch Freitag, 30. Dezember 2016 09:42

Donnerstag, 29. Dezember 2016 18:12

0

Anmelden

Hallo Jörg,

Respekt, 'Great Job' !!!! Da hast du dir aber sehr viel Mühe gegeben.

Funzt genauso wie es sein soll. :-)

Ich hatte auch mit "UNION" probiert, bin aber damit nicht weiter gekommen.

Vielleicht könntest du mir noch deine Lösungsidee mit den LEFT JOIN's erläutern, da bei mir die Anzahl der ProduktPakete variable zwischen 1 und 3 liegen kann. Dann kann ich mir Gedanken machen, wie man das variabel gestalten kann.

Gruß Jürgen

Freitag, 30. Dezember 2016 05:26

Antworten

|

Zitieren
0

Anmelden
Hallo Jürgen,

JOIN-Varaianten sind hier recht gut erklärt:

https://de.wikibooks.org/wiki/Einf%C3%BChrung_in_SQL:_Mehr_zu_JOIN

Ich hoffe Du siehst auch, dass ich Deine Ausgangstabelle umgeformt habe. Mit dem Design kam ich nicht weiter.

SELECT ProduktPaket1 AS Produkt, Filiale, 1 AS Produktpaket FROM Verkäufe UNION ALL SELECT ProduktPaket2 AS Produkt, Filiale, 2 AS Produktpaket FROM Verkäufe
Wenn ein Beitrag Deine Frage beantwortet und / oder hilfreich ist, wäre es nett, wenn Du diese entsprechend kennzeichnest. Wenn jemand ein ähnliches Problem hat, findet er die mögliche Lösung schneller; ferner sehen andere Unterstützer schneller, dass Dein Problem gelöst wurde und müssen sich nicht erst mühsam durch die Texte lesen.

Schönen Tag.
- Bearbeitet Joerg_x Freitag, 30. Dezember 2016 08:35
Freitag, 30. Dezember 2016 08:31

Antworten

|

Zitieren
0

Anmelden

Alles Klar Jörg, hab deine Antwort als beantwortet markiert.

danke nochmal.

Gruß Jürgen

Freitag, 30. Dezember 2016 09:44

Antworten

|

Zitieren

Produkte

Resources

Solutions

Updates

Testversionen

Verwandte Websites

Training

Zertifizierungen

Weitere Ressourcen

Für Produkte

Mehr Support

Kein IT-Experte?

Benutzer mit den meisten Antworten

Wie fasst man Spalten einer Tabelle zusammen und zählt das Vorkommen der einzelnen Werte in den einzelnen Spalten?

Frage

Antworten

Alle Antworten