MS-Office-Forum
Google
   

Zurück   MS-Office-Forum > Microsoft Access & Datenbanken > Microsoft Access - Code Archiv
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads Der Renner, 11 Entwicklertools für Access, Tipps & Trick und offene Datenbanken zum einzigartigen Preis.
Themen-Optionen Ansicht
Alt 17.12.2016, 15:08   #1
ebs17
MOF Guru
MOF Guru
Standard Grundlagen - SQL ist leicht (9) - Pivottabelle in Listtabelle umformen

Matrixen (Pivottabellen) werden sehr gern verwendet und kommen daher häufig vor (Beispiel als Bild dargestellt). In einer Datenbankumgebung sind sie als Ausgabe von Berechnungen denkbar (=> Kreuztabellenabfragen), in Excel werden sie gern und häufig gebraucht. Aber als Ausgangspunkt für eine Datenverarbeitung (speichernde Tabellen) sind Pivottabellen praktisch unbrauchbar, weil ein Filtern, Sortieren, Gruppieren und Aggregieren von eigentlich zusammengehörigen Daten, die auf mehrere Spalten verteilt ist, nur äußerst sperrig möglich ist.

Zur Umformung ist die Verwendung einer UNION-Abfrage denkbar. Diese hat aber entscheidende Nachteile: Einerseits muss die Anzahl der SELECT-Elemente zwischen den UNION-Schlüsselworten der Anzahl der Aufzählungsfelder in der Pivottabelle angepasst werden, die Notwendigkeit von Designänderungen wegen verringerter oder erhöhter Anzahl von Daten (hier dann auch von Spalten) ist immer problematisch. Andererseits bietet eine UNION-Abfrage als Ausgangspunkt von aufbauenden Auswertungen keine Indexnutzung und ist so ein Performanceproblem bei gewichtigen Berechnungen.

Alternative ist die Erstellung einer neuen Tabelle aus der Umformung. Das gespeicherte Ergebnis der Umformung ist eine performantere Basis für aufbauende Auswertungen. Das kann ausgebaut werden, wenn man Felder zusätzlich gezielt indiziert.
Wenn man die zu importierenden Daten nachhaltig speichern und verarbeiten will und sich so die Überführung in ein differenzierteres Datenmodell ergibt, ist diese Listtabelle eine gute Ausgangsbasis dafür.
In der Anlage wird über eine Funktion der Versuch unternommen, über einige zu wählende Argumente eine einigermaßen universelle Umformung einer Pivottabelle in eine Listtabelle mit wenig sonstigem Aufwand vorzunehmen.
Code:

' Deklaration der Funktion
Public Function PivotToList(ByVal NamePivotTable As String, _
                            ByVal NameListTable As String, _
                            ByVal NumberFirstMatrixField As Byte, _
                            ByVal NameTitleField As String, _
                            ByVal NameValueField As String, _
                            ByVal TypeValuefield As DataTypeEnum, _
                            Optional ByVal UseNullValues As Boolean = False, _
                            Optional ByVal IntoNewTable As Boolean = False) As Boolean
Angehängte Grafiken
Dateityp: png PivotToList.png (7,8 KB, 48x aufgerufen)
Angehängte Dateien
Dateityp: zip acSample_PivotToList.zip (24,2 KB, 35x aufgerufen)

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}
Dein Dankeschön: DBWiki=>Spende
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 18.12.2016, 09:31   #2
JPA
MOF Koryphäe
MOF Koryphäe
Standard

Coole Sache, vielen Dank.

Da Du Excel ansprichst, powerquery kann auch unpivot.

Gruß
JPA
JPA ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 03.07.2017, 09:54   #3
ebs17
Threadstarter Threadstarter
MOF Guru
MOF Guru
Standard

Der Code der verwendeten Funktion enthält in obiger Anlage noch einen Unterlassungsfehler. Da Recordsetfelder wie üblich bei Auflistungen 0-basiert gezählt werden, muss man das bei der Schleife (Übertragung der Matrixinhalte) berücksichtigen.
Korrektur ist farbig markiert:
Code:

Sub beispielaufruf_PivotToList()
    Dim bRet As Boolean

    ' Verknüpfen einer Exceltabelle
    DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, "Pivottabelle_XL", _
                              CurrentProject.Path & "Pivottabelle_Beispiel.xls", True

    bRet = PivotToList("Pivottabelle_XL", "Listtabelle", 4, "Art", "Betrag", dbLong, False, False)
    If bRet Then Debug.Print "Die Tabellenerstellung sollte geklappt haben."

    ' Entknüpfen der Exceltabelle
    DoCmd.DeleteObject acTable, "Pivottabelle_XL"
    
End Sub

Public Function PivotToList(ByVal NamePivotTable As String, _
                            ByVal NameListTable As String, _
                            ByVal NumberFirstMatrixField As Byte, _
                            ByVal NameTitleField As String, _
                            ByVal NameValueField As String, _
                            ByVal TypeValuefield As DataTypeEnum, _
                            Optional ByVal UseNullValues As Boolean = False, _
                            Optional ByVal IntoNewTable As Boolean = False) As Boolean
    On Error GoTo ErrHandler
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim sSQL As String
    Dim sConstantFields As String
    Dim bExistsTable As Boolean
    Dim i As Long

    Set dbs = CurrentDb

    bExistsTable = TableExistsDAO(dbs, NameListTable)
    If IntoNewTable Then
        If bExistsTable Then dbs.TableDefs.Delete NameListTable
    End If

    Set rst = dbs.OpenRecordset(NamePivotTable, dbOpenSnapshot)
    With rst
        If Not bExistsTable Or IntoNewTable Then
            ' Listtabelle neu erstellen
            Set tdf = dbs.CreateTableDef(NameListTable)
            For i = 0 To NumberFirstMatrixField - 2
                Set fld = tdf.CreateField(.Fields(i).Name, .Fields(i).Type)
                tdf.Fields.Append fld
                sConstantFields = sConstantFields & "[" & .Fields(i).Name & "], "
            Next
            Set fld = tdf.CreateField(NameTitleField, dbText)
            tdf.Fields.Append fld
            Set fld = tdf.CreateField(NameValueField, TypeValuefield)
            tdf.Fields.Append fld
            dbs.TableDefs.Append tdf
            RefreshDatabaseWindow
        End If

        ' Inhalte übertragen
        sConstantFields = ""
        For i = 0 To NumberFirstMatrixField - 2
            sConstantFields = sConstantFields & "[" & .Fields(i).Name & "], "
        Next
        For i = NumberFirstMatrixField - 1 To .Fields.Count - 1

            sSQL = "INSERT INTO " & NameListTable & " (" & sConstantFields & "[" & _
                   NameTitleField & "], [" & NameValueField & "])" & _
                   " SELECT " & sConstantFields & "'" & .Fields(i).Name & "', [" & _
                   .Fields(i).Name & "] FROM " & NamePivotTable
            If Not UseNullValues Then
                sSQL = sSQL & " WHERE [" & .Fields(i).Name & "] IS NOT NULL"
            End If
            dbs.Execute sSQL, dbFailOnError
        Next

        .Close
    End With

    '    ' Beispiel für ein Setzen eines zusammengesetzten Index
    '    sSQL = "CREATE INDEX NachnameVorname ON Listtabelle(Nachname, Vorname)"
    '    dbs.Execute sSQL, dbFailOnError

    Set rst = Nothing
    Set dbs = Nothing

    PivotToList = True

Exit_Function:
    Exit Function
ErrHandler:
    MsgBox "Fehler: " & vbTab & Err.Number & vbCrLf & Err.Description
    Resume Exit_Function
End Function

Public Function TableExistsDAO(pDb As DAO.Database, _
                               ByVal psName As String) As Boolean
    Dim s As String

    On Error Resume Next
    s = pDb.TableDefs(psName).Name
    TableExistsDAO = (Err.Number = 0)
End Function

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}
Dein Dankeschön: DBWiki=>Spende

Geändert von ebs17 (03.07.2017 um 09:56 Uhr).
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 13.09.2017, 07:36   #4
ebs17
Threadstarter Threadstarter
MOF Guru
MOF Guru
Standard

Ein Link zur Komplettierung: Kreuztabelle pivot-fähig umgestalten

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}
Dein Dankeschön: DBWiki=>Spende
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 06:12 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

Copyright ©2000-2010 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günther Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.
Beachten Sie bitte auch unsere Nutzungsbedingungen.