SQL Server dynamische PIVOT-Abfrage?
Habe ich damit beauftragt worden, mit kommen mit einer Möglichkeit der übersetzung der folgenden Daten:
date category amount
1/1/2012 ABC 1000.00
2/1/2012 DEF 500.00
2/1/2012 GHI 800.00
2/10/2012 DEF 700.00
3/1/2012 ABC 1100.00
in die folgenden:
date ABC DEF GHI
1/1/2012 1000.00
2/1/2012 500.00
2/1/2012 800.00
2/10/2012 700.00
3/1/2012 1100.00
Den weißen Flecken können, werden Null-Werte oder Leerzeichen, entweder ist in Ordnung, und die Kategorien müssten dynamisch sein. Ein weiterer möglicher Nachteil dieser ist, dass wir die Ausführung der Abfrage in eine begrenzte Kapazität, was bedeutet, dass temporäre Tabellen sind. Ich habe versucht zu forschen und landete auf PIVOT
aber ich habe noch nie verwendet, bevor ich wirklich verstehe es nicht, trotz meiner besten Anstrengungen, um es herauszufinden. Kann jemand mich in die richtige Richtung?
Welche version von SQL Server bitte?
mögliche Duplikate von Schreiben Sie die erweiterten SQL-Select
mögliche Duplikate von Schreiben Sie die erweiterten SQL-Select
InformationsquelleAutor Sean Cunningham | 2012-05-01
Du musst angemeldet sein, um einen Kommentar abzugeben.
Dynamische SQL-PIVOT:
Ergebnisse:
Also \@cols werden müssen Zeichenfolge verkettet, richtig? Wir können nicht die Verwendung von sp_executesql und parameter-Bindung zu interpolieren \@cols? Obwohl wir konstruieren \@cols uns selbst, was, wenn es irgendwie enthalten schädliche SQL. Jede zusätzliche Schadensbegrenzende Schritte, die ich nehmen könnte, bevor die Verkettung und Ausführung?
Wie würden Sie die Sortierung der Zeilen und Spalten auf dieser?
Es gibt eine Vielzahl von Möglichkeiten - ob Sie wollte, zu Sortieren
@cols
dann könnte man entfernen, dieDISTINCT
und verwendenGROUP BY
undORDER BY
wenn Sie die Liste der@cols
.Nevermind, ich war putting die Reihenfolge nach an der falschen Stelle.
InformationsquelleAutor Taryn
Dynamische SQL-PIVOT -
Anderen Ansatz zum erstellen von Spalten string
Ergebnis
InformationsquelleAutor mkdave99
Ich weiß, diese Frage ist schon älter, aber ich war auf der Suche durch die Antworten und dachte, dass ich vielleicht in der Lage, zu erweitern, auf die "dynamische" Teil des Problems und vielleicht auch jemand helfen.
In Erster Linie baute ich diese Lösung ein problem zu lösen, ein paar Mitarbeiter waren mit unbeständig und große Daten-sets müssen geschwenkt werden schnell.
Diese Lösung erfordert die Erstellung einer gespeicherten Prozedur, also wenn das aus der Frage für Ihre Bedürfnisse bitte aufhören zu Lesen.
Dieses Verfahren ist in den key-Variablen einer pivot-Anweisung dynamisch erstellen pivot-Anweisungen für unterschiedliche Tabellen, Spaltennamen und-Aggregate. Die Statische Spalte wird als Gruppe von /identity-Spalte der pivot(dieser kann entfernt werden, aus dem code, wenn nicht notwendig, aber ziemlich Häufig in pivot-Anweisungen und war notwendig, um zu lösen das ursprüngliche Problem), die pivot-Spalte ist, wo das Ende der resultierenden Spalte Namen erzeugt werden, und der Wert der Spalte ist, was die aggregate angewendet werden. Die Tabelle parameter ist der name der Tabelle, einschließlich das schema (schema.tablename) dieser Teil des Codes könnte etwas lieben, weil es ist nicht so sauber wie ich es gerne hätte. Es funktionierte für mich, denn mein Einsatz war nicht öffentlich ausgerichtete und sql-Injektion war kein Problem. Die Aggregierten parameter akzeptieren alle standard-sql-Aggregatfunktionen 'AVG', 'SUM', 'MAX' etc. Der code wird auch standardmäßig auf MAX als ein Aggregat dies ist nicht notwendig, aber das Publikum war ursprünglich für den nicht verstehen, Gelenke und wurden in der Regel mit max als ein Aggregat.
Beginnen wir mit dem code zum erstellen der gespeicherten Prozedur. Dieser code sollte in allen Versionen von SSMS 2005 und oben, aber ich habe es nicht getestet in 2005 oder 2016, aber ich kann nicht sehen, warum es würde nicht funktionieren.
Als Nächstes werden wir unsere Daten bereit für das Beispiel. Ich habe die Daten Beispiel aus der akzeptierten Antwort mit der Zugabe von ein paar Daten-Elemente zu verwenden, die in dieser proof-of-concept zeigen die vielfältigen Ausgänge des Aggregats ändern.
Die folgenden Beispiele zeigen die vielfältigen Ausführung Aussagen zeigt sich die abwechslungsreiche Aggregate als einfaches Beispiel. Ich habe keine Möglichkeit zum ändern der statischen -, Dreh-und Wert-Spalten um das Beispiel einfach. Sie sollten in der Lage sein, einfach kopieren und fügen Sie den code zum starten messing mit es sich
Diese Ausführung liefert die folgenden Datensätze jeweils.
Leider nicht, nach meinem besten wissen, weil Sie nicht haben eine dynamische Struktur für eine TVF. Sie haben einen statischen Satz von Spalten in einer TVF.
InformationsquelleAutor SFrejofsky
Können Sie erreichen dies mithilfe von dynamischen TSQL (denken Sie daran, verwenden Sie QUOTENAME zur Vermeidung von SQL-injection-Angriffe):
Schwenkt mit Dynamischen Spalten in SQL Server 2005
SQL Server - Dynamische PIVOT-Tabelle - SQL-Injection
Obligatorischen Verweis auf Der Fluch und Segen von Dynamischem SQL
QUOTENAME
hilft nur, SQL-injection-Angriffe, wenn Sie akzeptieren, @Tabellenname als ein parameter von einem Benutzer, und anfügen an eine Abfrage wieSET @sql = 'SELECT * FROM ' + @tableName;
. Sie können bauen viele gefährdete dynamische SQL-strings undQUOTENAME
nicht ein lecken, um Ihnen zu helfen.Bitte Lesen Sie diese meta-Diskussion. Wenn Sie, entfernen Sie die hyperlinks, Ihre Antwort ist unvollständig.
Ich bin damit einverstanden, dass das zeigen der code ist mehr hilfreich, aber sagen Sie, dass es erforderlich ist, damit es eine Antwort? Ohne die links, meine Antwort ist "Sie erreichen das mit Hilfe der dynamischen TSQL". Die ausgewählte Antwort schlägt den gleichen Weg, mit dem zusätzlichen Vorteil, wenn Sie auch zeigen, wie es zu tun, das ist der Grund, warum es ausgewählt wurde, als die Antwort.
Ich up stimmten der gewählten Antwort (bevor es ausgewählt wird), weil es ein Beispiel und wird besser helfen, jemand neu. Allerdings denke ich, dass jemand neues sollte auch Lesen Sie die links, die ich angegeben habe, das ist, warum ich nicht entfernen Sie Sie.
InformationsquelleAutor davids
Da ist meine Lösung die Reinigung der unnecesary null-Werte
InformationsquelleAutor m0rg4n
Den code unten stellt die Ergebnisse, die ersetzt NULL zu null in der Ausgabe.
Erstellen der Tabelle und einfügen von Daten:
Abfrage zu generieren, die genauen Ergebnisse, die Sie ersetzt auch NULL mit Nullen:
AUSGABE :
InformationsquelleAutor Arockia Nirmal
Aktualisierte version für SQL Server 2017 mit STRING_AGG-Funktion zum erstellen der pivot-Spalte Liste:
InformationsquelleAutor nvogel