Pivot-Übersichten ( Kreuztabelle ) für Texte

asu
Autor
Offline
Moderator
Mehr
11 Jahre 7 Monate her - 11 Jahre 7 Monate her #814 von asu
Pivot-Übersichten ( Kreuztabelle ) für Texte wurde erstellt von asu
Hallo,

die Möglichkeit sogenannte Kreuztabellen oder Pivot-Tabellen mit Hilfe der UserQueries zu gestalten, ist ja bekannt, für Filial-Umsätze auf HGR oder WGR Basis oder für Zeitachsen, dafür gibt es ja genügend Beispiele.

Beim Versuch sich eine Filialübersicht über ein Textfeld mit Hilfe unserer Procedure "UserPivot" wird man aber scheitern, da diese in der vorliegenden Form nicht für Textfelder ausgelegt ist.

Die Lösung: Sie können sich eine zweite UserPivot-Procedure anlegen ( siehe unten ), und diese dann verwenden, wenn Sie sich eine Kreuztabelle über Textfelder gestalten wollen.

Gruß
asu
Code:
create PROCEDURE [dbo].[UserPivotText]  
/*   
    Parameter  
    @Aggregate_Function (optional)  
         Aggregat-Funktion für die Pivot-Spalte, Default ist SUM  
    @Aggregate_Column  
         Name der Spalte, über die das Aggregat gebildet werden soll  
    @Aggregate_Column2 (optional)
         Name einer weiteren Spalte, über die das Aggregat gebildet werden soll  
    @TableOrView_Name  
         Name der Tabelle oder der View, über die die Pivot-Tabelle erzeugt werden soll  
         Dies kann auch ein gültiges SQL-Statement sein, wobei dies dann in Klammern stehen und einen Alias haben muss
         Beispiel '(SELECT  FROM UserLieferantenUmsatz WHERE (Filialnummer between 150 and 200) AND Periode=' + '''' + @Periode + ''') UserQ'  
    @Select_Column  
         Name der Spalte, für die Zeilen zurückgeliefert werden  
    @Select_Column2  
         Name einer zusätzlichen Spalte, für die Zeilen zurückgeliefert werden sollen
         Beispiel: @Select_Column = WGKürzel, @Select_Column2 = ArtikelNr
    @Info_Column1  
         Optional: Name einer zusätzlichen Spalte, für die Zeilen zurückgeliefert werden.
         Der Wert ist nicht in der Gruppierung enthalten und muss zur @Select_Column im Verhältnis 1:1 stehen
         Beispiel: @Select_Column = ArtikelNr, @Info_Column1 = Kurztext
    @Info_Column2  
         Optional: Name einer zusätzlichen Spalte, für die Zeilen zurückgeliefert werden.
         Siehe @Info_Column1
    @Pivot_Column  
         Name der Spalte, die in die Spalten transformiert werden soll  
    @Percentage
         0 (default) Es werden keine Prozentanteile mit ausgegeben
         1 Es wird jeweils der prozentuale Anteil am Gesamt als eigene Spalte mit erzeugt
    @Sum_Type
         0 Keine Zeilen mit Zwischensummen in das Ergebnis einfügen
         1 (default) Zwischensummen bei jedem Wechsel der @Select_Column ausgeben
         2 Zwischensummen für jede Kombination aus @Select_Column und @Select_Column2 ausgeben
    @DEBUG  
         0 (default) Keine Ausgabe von Debug-Meldungen
         1 Es werden Debug-Meldungen ausgegeben  
      
    Beispiel
         Es gibt eine UserQuery UserAktionsFilter mit den Daten der Tabelle AktionsStatistik und zusätzlich ein paar Feldern aus dem Artikel zur Info.
         Diese filtert die Filiale 0 aus (da die Summe in der Pivot-Tabelle separat ermittelt wird) und filtert und gruppiert 
         die gewünschten Werte schon einmal vor. Dies ist die Datengrundlage für die Pivot-Tabelle.
         Wichtig ist, dass es sich um eine View handelt!

		CREATE VIEW dbo.UserAktionsFilter
		AS
		SELECT     dbo.AktionsStatistik.*, dbo.Artikel.Kurztext AS Kurztext, dbo.Artikel.WGKürzel AS WGKürzel, dbo.Artikel.ArtikelGruppe AS ArtikelGruppe
		FROM         dbo.AktionsStatistik INNER JOIN
		                      dbo.Artikel ON dbo.AktionsStatistik.ArtikelNr = dbo.Artikel.ArtikelNr
		WHERE     (dbo.AktionsStatistik.Filialnummer > 0) AND (dbo.AktionsStatistik.Periode = 'S')
		

         Die UserQuery, die dann aufgerufen werden muss, sieht dann etwa so aus
		CREATE PROCEDURE UserQueryAktionAuswertung(@AktionsName Varchar(15)) AS
		DECLARE @V nvarchar(4000)
		SET @V = '(SELECT * FROM UserAktionsFilter WHERE Aktionsname=' + '''' + @AktionsName + ''') UserQ'
		EXEC UserPivot 'SUM', 'VerkaufMenge', 'VerkaufBetrag', @V, 'WGKürzel', 'ArtikelNr', 'Kurztext', 'Artikelgruppe', 'Filialnummer', 1, 1

         Wie man sieht, kann man hier beliebig viele Parameter einbauen und auch weitere Bedingungen an die View übergeben.
         Im obigen Beispiel wird der prozentuale Anteil der Filiale am Gesamtumsatz mit diesem Artikel ebenfalls mit ausgegeben.
         
   
*/

  @Aggregate_Function varchar(30) = 'SUM',  
  @Aggregate_Column   varchar(255),  
  @Aggregate_Column2   varchar(255) = '',  
  @TableOrView_Name   varchar(1024),  
  @Select_Column      varchar(255),  
  @Select_Column2     varchar(255) = '', 
  @Info_Column1       varchar(255) = '', 
  @Info_Column2       varchar(255) = '', 
  @Pivot_Column       varchar(255),
  @Percentage	bit = 0,   
  @Sum_Type     integer = 1,
  @DEBUG      bit = 0  
 AS  
 SET NOCOUNT ON  
 DECLARE @TransformPart   varchar(8000)  
 DECLARE @TransformTmp    varchar(8000)
 DECLARE @SQLColRetrieval nvarchar(4000)  
 DECLARE @SQLSelectIntro  varchar(8000)  
 DECLARE @SQLSelectFinal  varchar(8000)
 DECLARE @ColName         varchar(255)  
 DECLARE @ColName2        varchar(255)  
 DECLARE @ColValue	  varchar(255)
 DECLARE @Pos 		  integer
 DECLARE @StartPos 	  integer
 DECLARE @ColValueList    varchar(8000)

 IF @Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG', 'STDEV', 'VAR', 'VARP', 'STDEVP')    
  BEGIN RAISERROR ('Ungültige Aggregat-Funktion: %s', 10, 1, @Aggregate_Function) END  
 ELSE  
 BEGIN
  IF @Sum_Type = 0
          /* Kein Grouping() wenn keine Summen gewünscht sind */
	  SELECT @SQLSelectIntro = 'SELECT ' + QUOTENAME(@Select_Column) +  ', '  
	ELSE  
	  SELECT @SQLSelectIntro = 'SELECT CASE WHEN (GROUPING('  +   
	          QUOTENAME(@Select_Column)       +  
	     ') = 1) THEN ''Total'' ELSE '   +  
	     'CAST( + '                      +   
	                                 QUOTENAME(@Select_Column)       +  
	     ' AS varchar(255)) END As '    +   
	     QUOTENAME(@Select_Column)       +  
	     ', '  

  IF @Select_Column2 <> '' 
  BEGIN
     IF @Sum_Type = 0
          /* Kein Grouping() wenn keine Summen gewünscht sind */
	  SELECT @SQLSelectIntro = @SQLSelectIntro + ' ' + QUOTENAME(@Select_Column2) +  ', '  
	ELSE  
	  SELECT @SQLSelectIntro = @SQLSelectIntro +  
          ' CASE WHEN (GROUPING('  +   
          QUOTENAME(@Select_Column2)      +  
          ') = 1) THEN ''Total'' ELSE '   +  
          'CAST( + '                      +   
          QUOTENAME(@Select_Column2)      +  
          ' AS varchar(255)) END As '    +   
          QUOTENAME(@Select_Column2)      +  
          ', '
  END

  IF @Info_Column1 <> '' 
     IF @Sum_Type = 0
          /* Kein Grouping() wenn keine Summen gewünscht sind */
	  SELECT @SQLSelectIntro = @SQLSelectIntro + 
	     'MIN( '                        +   
             QUOTENAME(@Info_Column1)       +  
	     ') As '    +   
	     QUOTENAME(@Info_Column1)       +  
	     ', '  
	ELSE  
	  SELECT @SQLSelectIntro = @SQLSelectIntro +  
             ' CASE WHEN (GROUPING('  +   
             QUOTENAME(@Select_Column)      +  
             ') = 1 OR GROUPING('  +   
             QUOTENAME(@Select_Column2)      +  
             ') = 1) THEN '''' ELSE '  +  
	     'MIN( '                        +   
             QUOTENAME(@Info_Column1)       +  
	     ') END As '    +   
	     QUOTENAME(@Info_Column1)       +  
	     ', '  

  IF @Info_Column2 <> '' 
     IF @Sum_Type = 0
          /* Kein Grouping() wenn keine Summen gewünscht sind */
	  SELECT @SQLSelectIntro = @SQLSelectIntro + 
	     'MIN( '                        +   
             QUOTENAME(@Info_Column2)       +  
	     ') As '    +   
	     QUOTENAME(@Info_Column2)       +  
	     ', '  
	ELSE  
	  SELECT @SQLSelectIntro = @SQLSelectIntro +  
             ' CASE WHEN (GROUPING('  +   
             QUOTENAME(@Select_Column)      +  
             ') = 1 OR GROUPING('  +   
             QUOTENAME(@Select_Column2)      +  
             ') = 1) THEN '''' ELSE '  +  
	     'MIN( '                        +   
             QUOTENAME(@Info_Column2)       +  
	     ') END As '    +   
	     QUOTENAME(@Info_Column2)       +  
	     ', '  

  IF @DEBUG = 1 PRINT 'Intro :' + @SQLSelectIntro  

  /* Ermitteln der Werte, die in der Pivot-Spalte vorkommen.
     Die Liste der Werte wird als String in der Form [Wert1]@[Wert2]@ ... zurückgeliefert */

  SET @ColValueList = ''
  SET @SQLColRetrieval = 'SELECT @ColValueList = @ColValueList + QUOTENAME(CONVERT(VARCHAR(255),' +  
    QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +  '))+ ''@'' FROM ' +
    '(SELECT DISTINCT TOP 100 PERCENT' +   
    QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +   
    N' FROM ' + @TableOrView_Name + ' ORDER BY  ' +   
    QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) + ') SQI'
   IF @DEBUG = 1 PRINT @SQLColRetrieval

  EXEC sp_executesql @SQLColRetrieval,   
                            N'@ColValueList varchar(8000) OUTPUT',   
                            @ColValueList OUTPUT  
   
  IF @DEBUG = 1 PRINT @ColValueList
  IF @ColValueList = '' 
	  BEGIN 
            RAISERROR ('Keine Pivot-Spalten gefunden', 10, 1) 
            RETURN
	  END  


  /* Jetzt den String mit den Werten der Pivot-Spalte auseinandernehmen und
     für jedes Element den passenden SQL-Befehl erzeugen */

  IF @Select_Column2 <> '' 
     SET @Select_Column2 = ', ' + @Select_Column2 

  IF @Aggregate_Column2 <> '' 
	BEGIN 
           SET @ColName = ' (' + @Aggregate_Column + ')'
           SET @ColName2 = @Aggregate_Column + ' '
        END
  ELSE
	BEGIN 
           SET @ColName = ''
           SET @ColName2 = ''
        END

  SET @TransformPart = ''
  SET @StartPos=1
  SELECT @Pos = charindex(']@',@ColValueList) + 1
  WHILE @Pos>1
  BEGIN
	SELECT @ColValue = substring(@ColValueList,@StartPos+1,@Pos-@StartPos-2)
        SET @TransformTmp =  @Aggregate_Function + '(CASE CAST(' +   
	    QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +   
	    ' AS VARCHAR(255)) WHEN ''' + @ColValue + ''' THEN ' + @Aggregate_Column +  
	    ' ELSE ''0'' END) AS [' + @ColValue + @ColName + ']'    
        SET @TransformPart = @TransformPart + @TransformTmp

        IF @Percentage = 1
           BEGIN 
		  SET @TransformTmp = ', CASE WHEN ' + @Aggregate_Function + '(' +   
		    QUOTENAME(CAST(@Aggregate_Column AS VARCHAR(255))) + ') = 0 THEN 0 ELSE ' +
		    @Aggregate_Function + '(CASE CAST(' +   
		    QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +   
		    ' AS VARCHAR(255)) WHEN ''' + @ColValue + ''' THEN ' + @Aggregate_Column + 
		    ' ELSE ''0'' END) /' + @Aggregate_Function + '(' +   
		    QUOTENAME(CAST(@Aggregate_Column AS VARCHAR(255))) + ') * 100 END AS [' + @ColValue + ' (' + @ColName2 + '%)]'
                  SET @TransformPart = @TransformPart + @TransformTmp
            END
	 /* SQL-Statement für die 2. Aggregatspalte zusammenbasteln (sofern vorhanden) */ 
	 IF @Aggregate_Column2 <> ''
 	    BEGIN
	        SET @TransformTmp = ', ' + @Aggregate_Function + '(CASE CAST(' +   
		    QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +   
		    ' AS VARCHAR(255)) WHEN ''' + @ColValue + ''' THEN ' + @Aggregate_Column2 +  
		    ' ELSE ''0'' END) AS [' + @ColValue + ' (' + @Aggregate_Column2 + ')]'    
	        SET @TransformPart = @TransformPart + @TransformTmp
	
	        IF @Percentage = 1
	           BEGIN 
			  SET @TransformTmp = ', CASE WHEN ' + @Aggregate_Function + '(' +   
			    QUOTENAME(CAST(@Aggregate_Column2 AS VARCHAR(255))) + ') = 0 THEN 0 ELSE ' +
			    @Aggregate_Function + '(CASE CAST(' +   
			    QUOTENAME(CAST(@Pivot_Column AS VARCHAR(255))) +   
			    ' AS VARCHAR(255)) WHEN ''' + @ColValue + ''' THEN ' + @Aggregate_Column2 + 
			    ' ELSE ''0'' END) /' + @Aggregate_Function + '(' +   
			    QUOTENAME(CAST(@Aggregate_Column2 AS VARCHAR(255))) + ') * 100 END AS [' + @ColValue + ' (' + @Aggregate_Column2 + ' %)]'
	                  SET @TransformPart = @TransformPart + @TransformTmp
	            END
            END

        SET @StartPos = @Pos + 1
	SELECT @Pos = charindex(']@',@ColValueList,@Pos+1) + 1
        IF @Pos > 1 SET @TransformPart = @TransformPart + ', '
  END

  IF @DEBUG = 1 PRINT 'Transform: ' + @TransformPart

  SET @SQLSelectFinal = ', ' + @Aggregate_Function + '(' +   
                        CAST(@Aggregate_Column As Varchar(255)) +  
                        ') As [Total'+ @ColName + ']'

  IF @Aggregate_Column2 <> ''
     SET @SQLSelectFinal = @SQLSelectFinal + ', ' + @Aggregate_Function + '(' +   
                            CAST(@Aggregate_Column2 As Varchar(255)) +  
                            ') As [Total ('+ @Aggregate_Column2 + ')]'

  SET @SQLSelectFinal = @SQLSelectFinal + ' FROM ' + @TableOrView_Name + ' GROUP BY ' +   
                        @Select_Column + @Select_Column2 
  /* Zwischensummen hinzufügen
  ROLLUP => Nur Zwischensummen für die erste Select-Spalte
  CUBE   => Zwischensummen für alle Kombinationen
  */
  IF @Sum_Type = 1 SET @SQLSelectFinal = @SQLSelectFinal + ' WITH ROLLUP'
  IF @Sum_Type = 2 SET @SQLSelectFinal = @SQLSelectFinal + ' WITH CUBE'

  IF @DEBUG = 1 PRINT 'FinalPart :' + @SQLSelectFinal  
  IF @DEBUG = 1 PRINT 'SQL :' + @SQLSelectIntro + @TransformPart + @SQLSelectFinal
  EXEC (@SQLSelectIntro + @TransformPart + @SQLSelectFinal)  
 END
"Wer Rechtschreibfehler findet, darf sie behalten."
Bitte Anmelden oder Registrieren um der Konversation beizutreten.