PIVOT in MS SQL

  • VB6

Es gibt 4 Antworten in diesem Thema. Der letzte Beitrag () ist von Cell.

    PIVOT in MS SQL

    Hallo zusammen,

    ich steige gerade um von MS Access auf MS SQL Server. Dementsprechend laufen meine PIVOT abfragen nicht mehr

    Die Syntax in Access ist schematisch folgende:

    SQL-Abfrage

    1. TRANSFORM Funktion(Tabellenfeld) AS alias FROM Tabelle
    2. WHERE (where-Klausel)
    3. GROUP BY Feld
    4. ORDER BY Feld
    5. PIVOT Feld;


    Das klappt in MS SQL nicht. Hier ist die Syntax wie folgt (BSP von hier:

    SQL-Abfrage

    1. SELECT *
    2. FROM (
    3. SELECT
    4. year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month],
    5. InvoiceAmount as Amount
    6. FROM Invoice
    7. ) as s
    8. PIVOT
    9. (
    10. SUM(Amount)
    11. FOR [month] IN (jan, feb, mar, apr,
    12. may, jun, jul, aug, sep, oct, nov, dec)
    13. )AS pvt



    Mein PROBLEM ist, dass ich im FOR ... IN jetzt nich jeden möglich Wert (Im Bsp ja, feb,...) aufzählen möchte. Das bekomme ich im Access-Bsp. automatisch geliefert. Da ich mit ziemlich sicher bin, dass das hier auch gehen muss bitte ich um Hilfe.

    Vielen Dank
    Arne
    Geht soweit ich weiß nur mit dynamischen SQL.
    Siehe hier:
    databasejournal.com/features/m…npivot-in-sql-server.html

    Die Frage ist, wozu brauchst du es als Pivot? Vll geht es eh auch mit GROUP BY ?
    Das ist meine Signatur und sie wird wunderbar sein!
    Hallo Mono,

    zu deiner Frage: Aus der Abfrage wird ein Chart in einer Form erstellt. Für diesen Zweck ist eine Pivottabelle unschlagbar; vor allem, weil die Abfrage, insbesondere die Where-Klausel, dynamisch ist.

    das schaut kompliziert aus. Ich habe mich mittlerweile mal mit der genauen Syntax vertraut gemacht und habe eine (Krücken-) Lösung gefunden:
    Ich erstelle zuerst eine Abfrage mit SELECT Funktion(Feld), WHERE, GROUP BY, ORDER BY. Hierbei kommen alle unterschiedlichen Einträge in 'Feld' in ein DataTable.
    Die schreibe ich dann Kommagetrennt in einen String (In Meinem Fall z.B. "2017_01,2017_02,..."), Was dann in den IN-Teil eingesetzt wird.

    Dann sieht das grundsätzlich so aus wie oben in der zweiten SQL-Abfrage nur mit wesentlich umfangreicheren SELECT- und WHERE-Klauseln.
    Den ganzen Code zu posten wäre etwas umfangreich. Daher mal nur den Relevanten Teil:

    Visual Basic-Quellcode

    1. Private Sub Generate_Dia()
    2. If Not CheckForFieldEntries() Then Exit Sub
    3. Dim Zeilen As String = ""
    4. Dim Spalte As String
    5. Dim Wert As String
    6. Dim Funktion As String = Me.ID_SK_FU.SelectedValue
    7. Dim XFunktion As String = IIf(Me.XFunktion.SelectedValue <> "", Me.XFunktion.SelectedValue, "")
    8. Dim i As Integer
    9. With lvw_Zeilen
    10. For i = 0 To .Items.Count - 1
    11. Zeilen = Zeilen & ", " & getCompleteFieldName(.Items(i).ToString, "c")
    12. Next
    13. If Zeilen.Length > 2 Then
    14. Zeilen = Mid(Zeilen, 3)
    15. Else
    16. Exit Sub
    17. End If
    18. End With
    19. Spalte = getCompleteFieldName(Me.Spalte.Text, "c")
    20. Wert = getCompleteFieldName(Me.Wert.Text, "n")
    21. ' FROM string
    22. If IsNothing(Me.ID_SK_QY.SelectedValue) Then Exit Sub
    23. Dim ID_SK_QY As Integer = Me.ID_SK_QY.SelectedValue
    24. Dim s As String = "SELECT ID_SK_QY_TB, Condition FROM tbl_SK_QY WHERE ID_SK_QY =" & ID_SK_QY & ";"
    25. Dim dt As DataTable = OpenDT(s)
    26. Dim SQLfkt As String = dt.Rows(0).Item(0).ToString
    27. Dim Instanz As New cls_FrmSQL
    28. Dim sFROM As String = getFROMstring(CallByName(Instanz, SQLfkt, CallType.Method))
    29. Dim sWHERE As String = IIf(dt.Rows(0).Item(1).ToString > "", "WHERE " & dt.Rows(0).Item(1).ToString, "")
    30. 'Sonderfunktionen Übersetzen
    31. sWHERE = SonderfunktionenUebersetzen(sWHERE)
    32. Dim sGROUPBY As String = ""
    33. Dim Chart_Chart As Chart = Nothing
    34. Dim Chart_ChartArea As ChartArea = Nothing
    35. Dim Chart_Serie As Series = Nothing
    36. Dim Chart_Charttype As SeriesChartType = [Enum].Parse(GetType(SeriesChartType), Me.ID_SK_DI.Text)
    37. Dim Chart_Point As Integer = Nothing
    38. Dim Chart_PointValue As Object
    39. If Me.Diagrammfunktion.SelectedValue = 1 Then
    40. '[...]
    41. Else
    42. ' Kreuztabelle
    43. sGROUPBY = "GROUP BY " & Zeilen & " "
    44. Dim ORDERBY As String = IIf(Me.Dir.SelectedValue <> "ASC", "ORDER BY " & Zeilen & " DESC", "ORDER BY " & Zeilen & " ASC")
    45. Dim sPIVOT As String = ""
    46. If XFunktion = "" Or XFunktion = "keine" Then
    47. s = "(SELECT " & Zeilen & ", " & Spalte & " AS Spalten, " & Zeilen & " As Zeilen " &
    48. sFROM &
    49. sWHERE & " " &
    50. ") AS t"
    51. 'sGROUPBY &
    52. 'ORDERBY & " " & ") AS t"
    53. Else
    54. ' Liste Mit Spaltenüberschriften erstellen
    55. s = "SELECT Format(" & Spalte & ", '" & XFunktion & "') AS Spalten " & sFROM & sWHERE & " GROUP BY Format(" & Spalte & ", '" & XFunktion & "') ORDER BY Format(" & Spalte & ", '" & XFunktion & "')"
    56. Dim dt_Ueberschriften As DataTable = OpenDT(s)
    57. For Each U_Row As DataRow In dt_Ueberschriften.Rows
    58. sPIVOT = sPIVOT & ",[" & U_Row.Item(0).ToString & "]"
    59. Next
    60. sPIVOT = Mid(sPIVOT, 2)
    61. s = "(SELECT TOP (100) PERCENT " & Zeilen & ", Format(" & Spalte & ", '" & XFunktion & "') AS Spalten, " & Zeilen & " As Zeilen " &
    62. sFROM &
    63. sWHERE & " " &
    64. ORDERBY & " " & ") AS t"
    65. '") AS t"
    66. 'sGROUPBY &
    67. End If
    68. s = "SELECT * FROM " & s & " PIVOT(COUNT(Zeilen) For Spalten In(" & sPIVOT & ")) As p"
    69. dt = OpenDT(s)
    70. Try
    71. With Me.Chart1
    72. .ChartAreas.Clear()
    73. .Series.Clear()
    74. Chart_ChartArea = .ChartAreas.Add("new")
    75. Chart_ChartArea.AxisX.LabelStyle.Angle = 90
    76. Dim SerieName As String
    77. For Each row As DataRow In dt.Rows ' Zeilenweise
    78. SerieName = row.Item(0).ToString
    79. Chart_Serie = .Series.Add(SerieName)
    80. Dim a = 0
    81. .Series(SerieName).ChartType = Chart_Charttype
    82. For col As Integer = 1 To dt.Columns.Count - 1
    83. Dim ColName As String = dt.Columns(col).ColumnName
    84. 'If XFunktion = "" Or XFunktion = "keine" Then ColName = Format(ColName, XFunktion)
    85. Chart_PointValue = IIf(row.Item(col) Is DBNull.Value, 0, row.Item(col))
    86. Chart_Point = Chart_Serie.Points.AddXY(ColName, Chart_PointValue)
    87. Chart_Serie.Points(Chart_Point).Label = Chart_PointValue
    88. Next
    89. Next
    90. End With
    91. Catch ex As Exception
    92. MsgBox("Diagramm kann mit diesen Angaben nicht erstellt werden." & Chr(13) & Chr(13) & ex.Message)
    93. End Try
    94. End If
    95. End Sub
    Hallo zusammen,

    für die Nachwelt: Ich habe mal eine einigermaßen allgemeingültige Funktion geschrieben, die mit ein paar Angaben einen ausführbaren SQL-Befehl zurückgibt, mit dem eine Kreuztabelle erstellt wird. Ein kleines Extra sind die 'AndereZeilen' die vor der Pivottabelle eingesetzt werden.

    Aufrufbsp:

    Visual Basic-Quellcode

    1. Dim sFROM as string = "FROM tbl_TKZ LEFT JOIN tbl_TKZ_Values ON tbl_TKZ.ID_TKZ = tbl_TKZ_Values.ID_TKZ "
    2. Dim sWHERE As String = "WHERE tbl_TKZ.ID_UserTeam=" & ID_UserTeam & FilterAktturnus
    3. Dim s As String = sKreuztabelle("tbl_TKZ_Values.Wert", "tbl_TKZ_Values.Turnus", "keine", "Sum", sFROM, sWHERE, "ASC", "tbl_TKZ.ID_TKZ, tbl_TKZ.Teamkennzahl, tbl_TKZ.Soll")


    Modul:

    Visual Basic-Quellcode

    1. Module Kreuztabelle
    2. ''' <summary>
    3. ''' Erstellt aus einer normalen Abfrage eine Kreuztabelle
    4. ''' </summary>
    5. ''' <param name="Zeilen">Der Name des Tabellenfeldes, dessen Inhalt die Zeilen der Kreuztabelle geschrieben wird.</param>
    6. ''' <param name="Spalte">Der Name des Tabellenfeldes, aus dessen Inhalt n Wertspalten in der Kreuztabelle erstellt werden.</param>
    7. ''' <param name="XFunktion">Gültiger Formatierungsstring für die Werte in der Spalte zu gruppieren (z.B. 'yyyy' oder 'yyyy.MM').</param>
    8. ''' <param name="Funktion">Gültiger Funktionsname (sum, count....). Z.B. mit 'count' werden die Spaltenwerte mit gleichem Wert in 'Zeilen' gezählt.</param>
    9. ''' <param name="sFROM">Sql FORM-String (inklusive Join, etc.)</param>
    10. ''' <param name="sWHERE">Sql WHERE-String</param>
    11. ''' <param name="ORDERDIR">Optional: Sortierrichtung für dias Feld 'Zeilen' ('ASC' = Aufsteigend (Standard); 'DESC' = Absteigend)</param>
    12. ''' <param name="AndereZeilen">Optional: Weitere Felder, die angezeigt werden sollen (Standard = "").</param>
    13. ''' <returns>Ausführbarer SQL-String</Returns>
    14. Public Function sKreuztabelle(
    15. Zeilen As String, Spalte As String,
    16. XFunktion As String, Funktion As String,
    17. sFROM As String, sWHERE As String,
    18. Optional ORDERDIR As String = "ASC", Optional AndereZeilen As String = "") As String
    19. Dim sGROUPBY As String = "GROUP BY " & Zeilen & " "
    20. Dim ORDERBY As String = "ORDER BY " & Zeilen & " " & ORDERDIR
    21. Dim sPIVOT As String = ""
    22. Dim s As String
    23. Dim dt_Ueberschriften As DataTable
    24. Dim AngezeigteZeilen As String
    25. If AndereZeilen = "" Then
    26. AngezeigteZeilen = Zeilen
    27. Else
    28. AngezeigteZeilen = AndereZeilen
    29. End If
    30. 'Wird keine X-Funktion angegeben, wird ein vereinfachter code ausgeführt.
    31. If XFunktion = "" Or XFunktion = "keine" Then
    32. ' Alle unterschiedlichen Werte in 'Spalte' abfragen (Ergibt gleichzeitg Spaltenüberschriften)
    33. s = "SELECT " & Spalte & " AS Spalten " & sFROM & " " & sWHERE & " GROUP BY " & Spalte & " ORDER BY " & Spalte
    34. dt_Ueberschriften = OpenDT(s)
    35. ' ...Alle ermittelten unterschiedlichen Werte Kommagetrennt in einen String (sPIVOT) einfügen. Teilstring wird unten in FOR....IN() eingefügt.
    36. For Each U_Row As DataRow In dt_Ueberschriften.Rows
    37. sPIVOT = sPIVOT & ",[" & U_Row.Item(0).ToString & "]"
    38. Next
    39. s = "(SELECT " & AngezeigteZeilen & ", " & Spalte & " AS Spalten, " & Zeilen & " As Zeilen " &
    40. sFROM & " " &
    41. sWHERE & " " &
    42. ") AS t"
    43. Else ' Mit Format
    44. ' Alle unterschiedlichen Werte in 'Spalte' abfragen (Ergibt gleichzeitg Spaltenüberschriften)
    45. s = "SELECT Format(" & Spalte & ", '" & XFunktion & "') AS Spalten " & sFROM & sWHERE & " GROUP BY Format(" & Spalte & ", '" & XFunktion & "') ORDER BY Format(" & Spalte & ", '" & XFunktion & "')"
    46. dt_Ueberschriften = OpenDT(s)
    47. ' ...Alle ermittelten unterschiedlichen Werte Kommagetrennt in einen String (sPIVOT) einfügen. Teilstring wird unten in FOR....IN() eingefügt.
    48. For Each U_Row As DataRow In dt_Ueberschriften.Rows
    49. sPIVOT = sPIVOT & ",[" & U_Row.Item(0).ToString & "]"
    50. Next
    51. ' Ausdruck "TOP (100) PERCENT" leider notwendig, da sonst Fehler.
    52. s = "(SELECT TOP (100) PERCENT " & AngezeigteZeilen & ", Format(" & Spalte & ", '" & XFunktion & "') AS Spalten, " & Zeilen & " As Zeilen " &
    53. sFROM & " " &
    54. sWHERE & " " &
    55. ORDERBY & " " & ") AS t"
    56. End If
    57. sPIVOT = Mid(sPIVOT, 2) ' Erstes Komma löschen
    58. If sPIVOT = "" Then 'Fehler
    59. Return ""
    60. Else
    61. ' Generierten SQL-String in Abfrage einbauen (-> AS t) und nachfolgend PIVOT (-> AS p) anhängen.
    62. s = "SELECT * FROM " & s & " PIVOT(" & Funktion & "(Zeilen) For Spalten In(" & sPIVOT & ")) As p"
    63. End If
    64. Return s
    65. End Function
    66. End Module
    Deine Vorgehensweise scheint mir ein wenig kompliziert zu sein

    ich habe das immer so gelöst, das ich die Quelltabelle in eine Datatable geladen habe. Anschließend bin ich diese Tabelle 2x mit einer For Each schleife durchlaufen.

    1.) Spalten erzeugen
    2.) Falls noch nicht vorhanden Row erzeugen. Ansonsten Werte in die entsprechenden Zellen schreiben bzw. ggf. vorher mit dem bereits vorhandenen wert summieren.