none
Ermitteln einer PivotTabelle in MSSQL-Server2017 RRS feed

  • Frage

  • Hallo zusammen,

    ich habe in einer SQL-Tabelle in MS-SQL-Server2017 folgende Daten gespeichert:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[HitTable$](
    	[Ort] [nvarchar](255) NULL,
    	[ProduktA1] [float] NULL,
    	[IsVorhanden1] [float] NULL,
    	[IsWerbung1] [float] NULL,
    	[ProduktA2] [float] NULL,
    	[IsVorhanden2] [float] NULL,
    	[IsWerbung2] [float] NULL,
    	[ProduktA3] [float] NULL,
    	[IsVorhanden3] [float] NULL,
    	[IsWerbung3] [float] NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[HitTable$] ([Ort], [ProduktA1], [IsVorhanden1], [IsWerbung1], [ProduktA2], [IsVorhanden2], [IsWerbung2], [ProduktA3], [IsVorhanden3], [IsWerbung3]) VALUES (N'A', 1111, 1, 0, 1112, 0, 0, NULL, NULL, NULL)
    INSERT [dbo].[HitTable$] ([Ort], [ProduktA1], [IsVorhanden1], [IsWerbung1], [ProduktA2], [IsVorhanden2], [IsWerbung2], [ProduktA3], [IsVorhanden3], [IsWerbung3]) VALUES (N'B', 2222, 1, 0, 1111, 1, 0, 1113, 0, 0)
    INSERT [dbo].[HitTable$] ([Ort], [ProduktA1], [IsVorhanden1], [IsWerbung1], [ProduktA2], [IsVorhanden2], [IsWerbung2], [ProduktA3], [IsVorhanden3], [IsWerbung3]) VALUES (N'A', 1211, 0, 0, 2222, 1, 1, 1111, 0, 0)
    INSERT [dbo].[HitTable$] ([Ort], [ProduktA1], [IsVorhanden1], [IsWerbung1], [ProduktA2], [IsVorhanden2], [IsWerbung2], [ProduktA3], [IsVorhanden3], [IsWerbung3]) VALUES (N'B', 3333, 1, 0, 1111, 1, 0, 2222, 1, 0)
    INSERT [dbo].[HitTable$] ([Ort], [ProduktA1], [IsVorhanden1], [IsWerbung1], [ProduktA2], [IsVorhanden2], [IsWerbung2], [ProduktA3], [IsVorhanden3], [IsWerbung3]) VALUES (N'C', 1211, 0, 0, 1111, 1, 1, NULL, NULL, NULL)
    

    Dabei sollen nun alle Produkte aus den 3 Spalten "ProduktA1", "ProduktA2" und "ProduktA3" ermittelt werden, die IsVorhanden=True haben, aufgeteilt nach Anzahl der Vorkommen in den einzelnen Orten, sortiert nach Anzahl der Orte und danach nach AnzahlGesamt, wie folgende Ergebnistabellen zeigen:

    Vorhanden in allen Orten (Mindestanzahl 1):

    Vorhanden nur in A und B Mindestanzahl 2:

    Vorhanden nur in Ort A:

    Vorhanden in allen Orten und IsWerbung=true:

    Da in unserer Produktiv-Datenbank mehrere Millionen Datensätze gespeichert sind, suche ich nach einer möglichst effektiven Möglichkeit mittels SQL-Anweisungen die Ergebnisse zu erzielen. Diese müssen dann über eine WebApi (ISS) an den Client gesendet werden.

    Welche Orte (MindestAnzahl) ausgewertet werden sollen oder ob die Option "IsWerbung" mit ausgewertet werden soll, wird vom Client zuvor angefordert. 

    Ich hoffe, dass ich hier jemanden finde, der mir beim meinem Problem helfen kann.

    Grüße Jürgen

    Sonntag, 16. September 2018 10:53

Antworten

  • Hallo Christoph,

    danke für deine Antwort.

    Aber dein SQL liefert nicht die gewünschte Ausgabe (s.u.), sondern:

    Ich habe jedoch eine Lösung gefunden mit:

    SELECT pvt.Produkt, [A] as [Ort A], [B] as [Ort B], [C] as [Ort C], 
    		[Anzahl gemeinsame Orte], [Gesamtzahl], [Ist A1], [Ist A2], [Ist A3]
    FROM
    (
    	SELECT Produkt, [Ort]
    	FROM
    	( 
    		SELECT [ProduktA1] AS Produkt, [Ort], 1 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung1] = '0' AND [Ort] <> N'' AND [ProduktA1] <> N''
    		UNION ALL 
    		SELECT [ProduktA2] AS Produkt, [Ort], 2 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung2] = '0' AND [Ort] <> N'' AND [ProduktA2] <> N''
    		UNION ALL 
    		SELECT [ProduktA3] AS Produkt, [Ort], 3 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung3] = '0' AND [Ort] <> N'' AND [ProduktA3] <> N''
    	) as x
    ) as p
    
    PIVOT
    (
    	COUNT(Ort)
    	FOR [Ort] in ([A],[B],[C])
    ) as pvt
    
    --Spalte [Gesamtzahl]
    LEFT JOIN 
    ( 
    	SELECT COUNT (ISNULL ([Ort], 0)) AS [Gesamtzahl], Produkt 
    	FROM 
    	( 
    		SELECT [ProduktA1] AS Produkt, [Ort], 1 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung1] = '0' AND [Ort] <> N'' AND [ProduktA1] <> N''
    		UNION ALL 
    		SELECT [ProduktA2] AS Produkt, [Ort], 2 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung2] = '0' AND [Ort] <> N'' AND [ProduktA2] <> N''
    		UNION ALL 
    		SELECT [ProduktA3] AS Produkt, [Ort], 3 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung3] = '0' AND [Ort] <> N'' AND [ProduktA3] <> N''
    	) AS x 
    	GROUP BY Produkt 
    	
    ) AS g ON g.Produkt = pvt.Produkt 
    
    --Spalte [Anzahl gem. Orte]
    LEFT JOIN 
    ( 
    	SELECT MAX (ISNULL (ROW, 0)) AS [Anzahl gemeinsame Orte],  Produkt 
    	FROM 
    	( 
    		SELECT [Ort], Produkt, ROW_NUMBER() OVER(PARTITION BY Produkt ORDER BY [Ort] ASC) AS [ROW] 
    		FROM 
    		( 
    			SELECT [ProduktA1] AS Produkt, [Ort], 1 AS Produkte 
    			FROM HitTable$
    			WHERE [IsWerbung1] = '0' AND [Ort] <> N'' AND [ProduktA1] <> N''
    			UNION ALL 
    			SELECT [ProduktA2] AS Produkt, [Ort], 2 AS Produkte 
    			FROM HitTable$
    			WHERE [IsWerbung2] = '0' AND [Ort] <> N'' AND [ProduktA2] <> N''
    			UNION ALL 
    			SELECT [ProduktA3] AS Produkt, [Ort], 3 AS Produkte 
    			FROM HitTable$
    			WHERE [IsWerbung3] = '0' AND [Ort] <> N'' AND [ProduktA3] <> N''
    		) AS x 
    		GROUP BY Produkt, [Ort] 
    	) AS v 
    	GROUP BY Produkt 
    	
    ) AS e on e.Produkt = g.Produkt
    
    --Spalte [Ist Produkt A1]
    LEFT JOIN 
    ( 
    	SELECT COUNT (ISNULL ([Ort], 0)) AS [Ist A1], Produkt 
    	FROM 
    	( 
    		SELECT [ProduktA1] AS Produkt, [Ort], 1 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung1] = '0' AND [Ort] <> N'' AND [ProduktA1] <> N''
    		UNION ALL 
    		SELECT [ProduktA2] AS Produkt, [Ort], 2 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung2] = '0' AND [Ort] <> N'' AND [ProduktA2] <> N''
    		UNION ALL 
    		SELECT [ProduktA3] AS Produkt, [Ort], 3 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung3] = '0' AND [Ort] <> N'' AND [ProduktA3] <> N''
    	) AS x 
    	WHERE Produkte = 1
    	GROUP BY Produkt 
    	
    ) AS a ON a.Produkt = pvt.Produkt
    
    --Spalte [Ist Produkt A2]
    LEFT JOIN 
    ( 
    	SELECT COUNT (ISNULL ([Ort], 0)) AS [Ist A2], Produkt 
    	FROM 
    	( 
    		SELECT [ProduktA1] AS Produkt, [Ort], 1 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung1] = '0' AND [Ort] <> N'' AND [ProduktA1] <> N''
    		UNION ALL 
    		SELECT [ProduktA2] AS Produkt, [Ort], 2 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung2] = '0' AND [Ort] <> N'' AND [ProduktA2] <> N''
    		UNION ALL 
    		SELECT [ProduktA3] AS Produkt, [Ort], 3 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung3] = '0' AND [Ort] <> N'' AND [ProduktA3] <> N''
    	) AS x 
    	WHERE Produkte = 2
    	GROUP BY Produkt 
    	
    ) AS b ON b.Produkt = pvt.Produkt
    
    --Spalte [Ist Produkt A3]
    LEFT JOIN 
    ( 
    	SELECT COUNT (ISNULL ([Ort], 0)) AS [Ist A3], Produkt 
    	FROM 
    	( 
    		SELECT [ProduktA1] AS Produkt, [Ort], 1 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung1] = '0' AND [Ort] <> N'' AND [ProduktA1] <> N''
    		UNION ALL 
    		SELECT [ProduktA2] AS Produkt, [Ort], 2 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung2] = '0' AND [Ort] <> N'' AND [ProduktA2] <> N''
    		UNION ALL 
    		SELECT [ProduktA3] AS Produkt, [Ort], 3 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung3] = '0' AND [Ort] <> N'' AND [ProduktA3] <> N''
    	) AS x 
    	WHERE Produkte = 3
    	GROUP BY Produkt 
    	
    ) AS c ON c.Produkt = pvt.Produkt
    
    ORDER BY [Anzahl gemeinsame Orte] DESC

    liefert dann die Ausgabe:

    Es sind noch Berechnungsspalten hinzugekommen, die aber für das eigentliche Problem unerheblich sind. 

    Jetzt stehe ich aber vor dem nächsten Problem:
    Die zu pivotierenden Orten müssen zuvor mit einem weiteren SQL ermittelt werden:
    SELECT DISTINCT Ort FROM HitTable$
    um diese dann in den Pivot-SQL zu übergeben.

    Ich habe es schon mit Parametern versucht bin aber nicht weiter gekommen. :-(

    Gruß Jürgen

    • Als Antwort markiert Jürgen Sch Dienstag, 18. September 2018 04:50
    Montag, 17. September 2018 14:12

Alle Antworten

  • Ggf. gibt es 2 Verfahren die du verwenden kannst:

    Um analytische Funktionen zu verwenden stehen die sog. "Window"-Funktionen zur Verfügung.
    Erreichbar sind diese per "Over"-Klausel:

    https://docs.microsoft.com/de-de/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017

    Würfel-Funktionen gibt es auch, hier ein paar Beispiele:

    https://docs.microsoft.com/de-de/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

    Sonntag, 16. September 2018 14:03
  • Hallo Jürgen,

    der erste Schritt dürfte ein UNPIVOT der Daten sein und die Verwendung geeigneter Datentypen.

    Da es keine zeitliche Komponente gibt, könnte man in einer weiteren Tabelle einfach die kumulierten Werte je Ort und Produkt vorhalten und bei neuen Sätzen entsprechend ergänzen. Falls es doch eine zeitliche Komponente gibt, müsstest Du hier ein geeignetes Aggregat finden (Tag, KW, Monat, ...).


    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    Montag, 17. September 2018 06:48
    Beantworter
  • Hallo bfuerchau,

    danke für die Rückmeldung. Leider konnte ich in den von dir angegebenen Links keine Lösung für mein Problem ermitteln.

    Schon für das PIVOT werden keine gültigen Werte angezeigt:

    SELECT [Produkt], [Ort A], [Ort B], [Ort C]
    FROM
    (
    	SELECT [Produkt], [Ort]
    	FROM 
    	( 
    		SELECT [ProduktA1] AS Produkt, [Ort], 1 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung1] = '0' AND [Ort] <> N'' AND [ProduktA1] <> N''
    		UNION ALL 
    		SELECT [ProduktA2] AS Produkt, [Ort], 2 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung2] = '0' AND [Ort] <> N'' AND [ProduktA2] <> N''
    		UNION ALL 
    		SELECT [ProduktA3] AS Produkt, [Ort], 3 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung3] = '0' AND [Ort] <> N'' AND [ProduktA3] <> N''
    	) as x
    ) as p
    
    PIVOT
    (
    	COUNT([ORT])
    	FOR [Ort] in ([Ort A],[Ort B],[Ort C])
    ) as pvt
    ORDER BY pvt.Produkt 

    siehe diese Ausgabe:

    Was läuft da falsch???

    Gruß Jürgen

    Montag, 17. September 2018 07:27
  • Am einfachsten geht es hier mit CROSS APPLY, da das UNPIVOT ja über mehrere Spalten gehen müsste.

    -- Mal zum gucken
    select Ort, c.Produkt, c.IsVorhanden
    from dbo.HitTable$
    cross apply
    (
      select ProduktA1, IsVorhanden1 union all
      select ProduktA2, IsVorhanden2  union all
      select ProduktA3, IsVorhanden3
    ) c (Produkt, Isvorhanden);
    
    -- Umsetzung
    With Vorberechnung as
    (select Ort, c.Produkt, c.IsVorhanden
    from dbo.HitTable$
    cross apply
    (
      select ProduktA1, IsVorhanden1 union all
      select ProduktA2, IsVorhanden2  union all
      select ProduktA3, IsVorhanden3
    ) c (Produkt, Isvorhanden))
    Select Ort, Produkt, Sum(IsVorhanden) as IsVorhanden
    from Vorberechnung
    where Produkt is not null
    group by Ort, Produkt
    order by Ort, Produkt;


    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    Montag, 17. September 2018 10:21
    Beantworter
  • Hallo Christoph,

    danke für deine Antwort.

    Aber dein SQL liefert nicht die gewünschte Ausgabe (s.u.), sondern:

    Ich habe jedoch eine Lösung gefunden mit:

    SELECT pvt.Produkt, [A] as [Ort A], [B] as [Ort B], [C] as [Ort C], 
    		[Anzahl gemeinsame Orte], [Gesamtzahl], [Ist A1], [Ist A2], [Ist A3]
    FROM
    (
    	SELECT Produkt, [Ort]
    	FROM
    	( 
    		SELECT [ProduktA1] AS Produkt, [Ort], 1 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung1] = '0' AND [Ort] <> N'' AND [ProduktA1] <> N''
    		UNION ALL 
    		SELECT [ProduktA2] AS Produkt, [Ort], 2 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung2] = '0' AND [Ort] <> N'' AND [ProduktA2] <> N''
    		UNION ALL 
    		SELECT [ProduktA3] AS Produkt, [Ort], 3 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung3] = '0' AND [Ort] <> N'' AND [ProduktA3] <> N''
    	) as x
    ) as p
    
    PIVOT
    (
    	COUNT(Ort)
    	FOR [Ort] in ([A],[B],[C])
    ) as pvt
    
    --Spalte [Gesamtzahl]
    LEFT JOIN 
    ( 
    	SELECT COUNT (ISNULL ([Ort], 0)) AS [Gesamtzahl], Produkt 
    	FROM 
    	( 
    		SELECT [ProduktA1] AS Produkt, [Ort], 1 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung1] = '0' AND [Ort] <> N'' AND [ProduktA1] <> N''
    		UNION ALL 
    		SELECT [ProduktA2] AS Produkt, [Ort], 2 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung2] = '0' AND [Ort] <> N'' AND [ProduktA2] <> N''
    		UNION ALL 
    		SELECT [ProduktA3] AS Produkt, [Ort], 3 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung3] = '0' AND [Ort] <> N'' AND [ProduktA3] <> N''
    	) AS x 
    	GROUP BY Produkt 
    	
    ) AS g ON g.Produkt = pvt.Produkt 
    
    --Spalte [Anzahl gem. Orte]
    LEFT JOIN 
    ( 
    	SELECT MAX (ISNULL (ROW, 0)) AS [Anzahl gemeinsame Orte],  Produkt 
    	FROM 
    	( 
    		SELECT [Ort], Produkt, ROW_NUMBER() OVER(PARTITION BY Produkt ORDER BY [Ort] ASC) AS [ROW] 
    		FROM 
    		( 
    			SELECT [ProduktA1] AS Produkt, [Ort], 1 AS Produkte 
    			FROM HitTable$
    			WHERE [IsWerbung1] = '0' AND [Ort] <> N'' AND [ProduktA1] <> N''
    			UNION ALL 
    			SELECT [ProduktA2] AS Produkt, [Ort], 2 AS Produkte 
    			FROM HitTable$
    			WHERE [IsWerbung2] = '0' AND [Ort] <> N'' AND [ProduktA2] <> N''
    			UNION ALL 
    			SELECT [ProduktA3] AS Produkt, [Ort], 3 AS Produkte 
    			FROM HitTable$
    			WHERE [IsWerbung3] = '0' AND [Ort] <> N'' AND [ProduktA3] <> N''
    		) AS x 
    		GROUP BY Produkt, [Ort] 
    	) AS v 
    	GROUP BY Produkt 
    	
    ) AS e on e.Produkt = g.Produkt
    
    --Spalte [Ist Produkt A1]
    LEFT JOIN 
    ( 
    	SELECT COUNT (ISNULL ([Ort], 0)) AS [Ist A1], Produkt 
    	FROM 
    	( 
    		SELECT [ProduktA1] AS Produkt, [Ort], 1 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung1] = '0' AND [Ort] <> N'' AND [ProduktA1] <> N''
    		UNION ALL 
    		SELECT [ProduktA2] AS Produkt, [Ort], 2 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung2] = '0' AND [Ort] <> N'' AND [ProduktA2] <> N''
    		UNION ALL 
    		SELECT [ProduktA3] AS Produkt, [Ort], 3 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung3] = '0' AND [Ort] <> N'' AND [ProduktA3] <> N''
    	) AS x 
    	WHERE Produkte = 1
    	GROUP BY Produkt 
    	
    ) AS a ON a.Produkt = pvt.Produkt
    
    --Spalte [Ist Produkt A2]
    LEFT JOIN 
    ( 
    	SELECT COUNT (ISNULL ([Ort], 0)) AS [Ist A2], Produkt 
    	FROM 
    	( 
    		SELECT [ProduktA1] AS Produkt, [Ort], 1 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung1] = '0' AND [Ort] <> N'' AND [ProduktA1] <> N''
    		UNION ALL 
    		SELECT [ProduktA2] AS Produkt, [Ort], 2 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung2] = '0' AND [Ort] <> N'' AND [ProduktA2] <> N''
    		UNION ALL 
    		SELECT [ProduktA3] AS Produkt, [Ort], 3 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung3] = '0' AND [Ort] <> N'' AND [ProduktA3] <> N''
    	) AS x 
    	WHERE Produkte = 2
    	GROUP BY Produkt 
    	
    ) AS b ON b.Produkt = pvt.Produkt
    
    --Spalte [Ist Produkt A3]
    LEFT JOIN 
    ( 
    	SELECT COUNT (ISNULL ([Ort], 0)) AS [Ist A3], Produkt 
    	FROM 
    	( 
    		SELECT [ProduktA1] AS Produkt, [Ort], 1 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung1] = '0' AND [Ort] <> N'' AND [ProduktA1] <> N''
    		UNION ALL 
    		SELECT [ProduktA2] AS Produkt, [Ort], 2 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung2] = '0' AND [Ort] <> N'' AND [ProduktA2] <> N''
    		UNION ALL 
    		SELECT [ProduktA3] AS Produkt, [Ort], 3 AS Produkte 
    		FROM HitTable$
    		WHERE [IsWerbung3] = '0' AND [Ort] <> N'' AND [ProduktA3] <> N''
    	) AS x 
    	WHERE Produkte = 3
    	GROUP BY Produkt 
    	
    ) AS c ON c.Produkt = pvt.Produkt
    
    ORDER BY [Anzahl gemeinsame Orte] DESC

    liefert dann die Ausgabe:

    Es sind noch Berechnungsspalten hinzugekommen, die aber für das eigentliche Problem unerheblich sind. 

    Jetzt stehe ich aber vor dem nächsten Problem:
    Die zu pivotierenden Orten müssen zuvor mit einem weiteren SQL ermittelt werden:
    SELECT DISTINCT Ort FROM HitTable$
    um diese dann in den Pivot-SQL zu übergeben.

    Ich habe es schon mit Parametern versucht bin aber nicht weiter gekommen. :-(

    Gruß Jürgen

    • Als Antwort markiert Jürgen Sch Dienstag, 18. September 2018 04:50
    Montag, 17. September 2018 14:12
  • Hallo Jürgen,

    dann musst Du dynamisches SQL verwenden:

    Dynamisches Pivot


    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    Dienstag, 18. September 2018 05:47
    Beantworter