Schwenkt mit dynamischen Spalten in SQL Server
Arbeite ich an einer SQL-Abfrage mit pvots mit dynamischen Spalten in SQL Server (T-sql).
Eher als Vorlage meine langwierige Abfrage, ich veranschaulichen mein problem mit einem vereinfachten Modell.
Ich erstelle 2 Tabellen: Tabelle1 und Tabelle2 und füllen Sie mit ein paar Eingaben wie folgt:
Tabelle1:
Col_ID1...............Col_Name
1.........................Jan-11
2.........................Feb-11
3.........................Mar-11
Tabelle2:
Col_ID2......Konto.....AccountName......Betrag
1...............121...........Strom............10000
2...............121...........Strom............20000
3...............121...........Strom............30000
1...............122...........Telefon..............100
2...............122...........Telefon..............200
3...............122...........Telefon..............300
Ich bin erstellen einer Pivot -, aber ich möchte die Spaltennamen generiert werden Parametrisch (basierend auf den Daten eingegeben, die aus der Eingabemaske), und nicht hart codiert.
Die folgende Abfrage funktioniert auch, aber gibt nur ein paar Spalten als foll:
Jan-11...........Feb-11...........Mar-11
10,000.00......20,000.00......30,000.00
100.00...............200.00...........300.00
Möchte ich die Abfrage der beschreibenden Spalten auch, als foll:
Konto...........AccountName...........Jan-11............Feb-11..............Mar-11
121.................Strom..................10,000.00......20,000.00..........30,000.00
122.................Telefon.....................100.00...........200.00.............300.00
Könnte jemand bitte helfen Sie mir, meine Abfrage ändern, so dass ich mein Ziel erreichen?
Diese Abfrage ist eine Anpassung der folgende Artikel, geschrieben von Dr. Andras im Sept 2007.
http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx
Jemand bemerkte, dass der Kodex unterworfen sein konnte-Injection-Angriffe und schlug Quotename-Funktion anstelle der Verkettung von eckigen Klammern.
Könnten Sie erläutern, wie Sie Quotename in meiner Abfrage.
Vielen Dank,
Leon Lai
.
.
.
Hier ist meine Abfrage:
------------------------ erstellen & Tabelle1 füllen --------------------------------
CREATE TABLE Table1
(Col_ID1 INT,
Col_Name varchar(10))
INSERT INTO Table1 VALUES (1, 'Jan-11')
INSERT INTO Table1 VALUES (2, 'Feb-11')
INSERT INTO Table1 VALUES (3, 'Mar-11')
-------------------------erstellen & Tabelle2 füllen ----------------------------------
CREATE TABLE Table2
(Col_ID2 INT,
Account varchar(10),
AccountName varchar(20),
Amount numeric(18,6))
INSERT INTO Table2 VALUES (1, 121, 'Electricity', 10000)
INSERT INTO Table2 VALUES (2, 121, 'Electricity', 20000)
INSERT INTO Table2 VALUES (3, 121, 'Electricity', 30000)
INSERT INTO Table2 VALUES (1, 122, 'Telephone', 100)
INSERT INTO Table2 VALUES (2, 122, 'Telephone', 200)
INSERT INTO Table2 VALUES (3, 122, 'Telephone', 300)
---------------------------------- erstellen von Spalten-überschriften -------------------
DECLARE @cols NVARCHAR(2000)
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + t2.Col_Name
FROM Table1 AS t2
ORDER BY '],[' + t2.Col_Name
FOR XML PATH('')
), 1, 2, '') + ']'
------------------------------------- erstellen @query ----------------------
DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT '+
@cols +'
FROM
--------------------------Unterabfrage-----
(SELECT
t1.Col_Name,
t2.Account,
t2.Amount
FROM Table1 AS t1
JOIN Table2 AS t2 ON t1.Col_ID1 = t2.Col_ID2
) p
--------------------pivot -------------------------
PIVOT
(
Sum ([Amount] )
FOR Col_Name IN
( '+
@cols +' )
) AS pvt '
----------------------exec & drop----------
EXECUTE(@query)
drop table table1
drop table table2
=======================================================
Hallo Philip,
Vielen Dank für Ihre Antwort.
Ihre vorgeschlagene Abfrage funktioniert reibungslos, und erzeugt das erwartete Bild, aber ist es nicht genau das, was ich wollte.
Erste, vielen Dank für den code:
SELECT @cols = isnull (@cols + ',' , '' ) + '[' + Col_Name + ']'
Ist es einfacher und ersetzt meine Linie mit Sachen-und xml-Pfad, anscheinend mit dem gleichen Effekt.
Lassen Sie mich erklären, was ich tun will.
Möchte ich entwickeln eine Abfrage, die in Sap Business 1 (Ein Buchhaltungs - oder nennen Sie es eine ERP).
Sap verwendet T-sql in Microsoft Server 2008 und verfügt über einen eigenen Abfragegenerator.
Mit sehr wenigen Ausnahmen, Sap sql ist ähnlich zu T-sql.
Möchte ich meine Abfrage gibt eine Liste aller Einnahmen und Ausgaben Monat für Monat über einen Zeitraum von 12 Monaten.
Aber ich will nicht, dass meine Spaltenüberschriften, um die hardcoded werden, (wie dies erforderlich wäre, mich zu ändern, meine Anfrage von Zeit zu Zeit) wie folgt:
Jan-11 Feb-11 Mar-11, Apr-11, ..... Dez-11
Vielmehr möchte ich die Spalten-überschriften werden Dynamisch generiert aus den Daten, die der Benutzer in die Eingabemaske.
Wie gesagt, die Abfrage die ich gepostet auf dem forum ist eine über-vereinfachte version von meine echte Abfrage, nur zur illustration. Die eigentliche Abfrage mit mehreren Variablen und einer Eingabe-Bildschirm (sogenannte Query - Auswahl-Kriterien-box in Sap-b1) erlaubt dem Benutzer ein Datum eingeben. Es ist dieses Datum, das verwendet wird, um zu bestimmen, die Spaltennamen dynamisch.
Dies ist der Grund, warum ich brauchte, solche komplizierten Werkzeugen wie @cols, @query, pivot etc.
Wenn ich input, sagen Sie '01.06.11' (01 Jun 2011) in der Eingabemaske dieses Datum wird an die sql -, die bestimmen werden, die Namen der Spalten-überschriften als foll:
Jun-11, Jul-11 Aug-11 ..... Mai-12.
Wenn ich die Eingabe ein Datum, sagen wir '01.09.10' (01 Sep 2010), die Spaltenüberschriften zu ändern:
Sep-10 Oct-10, .... Aug-11
Scheint es, dass Sie hardcoded meine Spaltenüberschriften.
Könnten Sie einen zweiten Blick auf meine Abfrage, und etwas vorzuschlagen, mit denen die Spaltennamen generiert werden Parametrisch, anstatt hart codiert?
Dank
Leon Lai
Du musst angemeldet sein, um einen Kommentar abzugeben.
Hinzufügen von Spalten ist sehr einfach. Die Letzte Abfrage wäre
die t2.AccountName Hinzugefügt, um die Unterabfrage, und Konto und AccountName Hinzugefügt, um die erste WÄHLEN. Werfen Sie in der build-Anweisung und fertig:
Als für SQL-injection, nur so kann ich sehen, dass passiert ist, wenn jemand irgendwie bettet bösartigen code in Tabelle1.Col_Name, und wenn Sie befürchten, dass Sie größere Probleme haben als "sperren" diese dynamische Abfrage.
Auch noch erwähnenswert ist, ich würde verwenden Sie die folgenden zu bauen, die Liste der Spalten (@Cols), weil die kürzer und leichter zu Lesen, sondern vor allem, weil ich nicht wie bei XML.
quotename(Col_Name)
statt'[' + Col_Name + ']'
.Hinzufügen einer anderen Antwort, da dies das Bearbeiten fast eine zweite Frage. (Ohne details und Besonderheiten, kann ich nur Allgemeine Umrisse und Pseudo-code—ich weiß nicht, SAP.)
Beginnen wir mit dem Dreh-und Angelpunkt. Es muss zu generieren Spalten gekennzeichnet durch, vermutlich, den Monat, den Sie hatte in Ihrem Beispiel als "Tabelle1".Col_Name, ein varchar(10); die Werte werden extrahiert und dynamisch Hinzugefügt, um die pivot-Abfrage, die als Spaltennamen. Wenn es keine solche Spalte in der Datenbank, dann müssen Sie erstellen Sie für die Abfrage auf der Grundlage der Daten, die der Anwender eingibt. Ich arbeite mit den folgenden Annahmen:
- Die Daten hat ein dateime Spalte, wo jeder Wert (Jahr, durch Millisekunde) gefunden werden kann
- Der Benutzer gibt ein "start-Datum" (ist es immer der erste des Monats?) und Sie generieren Spalten für diese und die folgenden 11 Monate, Aggregation der Daten, das fällt innerhalb der einzelnen Ziel-Monat.
Schritt 1, würde ich einrichten und Auffüllen einer temp Tabelle enthält die 12 Ziel-Spalten:
Label formatiert ist, wie Sie es angezeigt werden soll, MonthStart wäre der absolute Anfang des Monats (sagen wir, Okt 1, 2011 00:00:00.000), und MonthEnd wäre der absolute Beginn des nächsten Monats (Nov. 1, 2011 00:00:00.000) – dies ermöglicht die Verwendung
SELECT … from <table> where DataDate >= MontStart and DataDate < MonthEnd
um alle Daten innerhalb dieses Monats.Nächsten, besuchen Sie diese Tabelle auf Ihre Daten-Tabelle und aggregieren, so etwas wie:
Stecken Sie diese in als die innerste Abfrage der pivot-Anweisung extrahieren/erstellen die Liste der Col_Names aus der temp-Tabelle mit den nicht-XML-Abfragen (ich weiß nicht, was anderes zu nennen), erstellen und führen Sie dynamisch, und Sie sollten gut sein.