PDA

Vollständige Version anzeigen : SQL ist leicht (9) - Pivottabelle in Listtabelle umformen


ebs17
17.12.2016, 15:08
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.
' 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

JPA
18.12.2016, 09:31
Coole Sache, vielen Dank.

Da Du Excel ansprichst, powerquery kann auch unpivot (https://support.office.com/en-us/article/Unpivot-columns-Power-Query-0f7bad4b-9ea1-49c1-9d95-f588221c7098).

Gruß
JPA

ebs17
03.07.2017, 09:54
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:
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

ebs17
13.09.2017, 07:36
Ein Link zur Komplettierung: Kreuztabelle pivot-fähig umgestalten (https://is.gd/2eCKQw)