Frage an sql-server, coalesce, tsql, sql-server-2008 – Verketten Sie mehrere Zeilen aus mehreren Tabellen

2

Ich habe überprüftviele andere Ich habe hier etwas geschrieben und bin mit der Coalesce-Funktion ziemlich vertraut geworden, aber ich konnte nicht herausfinden, wie ich diese bestimmte Aufgabe ausführen soll.

Ich habe also eine Provisions- und eine Kategorietabelle. Ich habe eine erstellthier So können Sie die genaue Datenstruktur mit einigen Beispieldaten sehen. Grundsätzlich enthält die Commission-Tabelle die Spalten SalesRepID, LocationID, CategoryID, SurgeonID und CommissionPercent.

Verwenden einer Coalesce-FunktionIch konnte so etwas durch Übergabe der SalesRepID, LocationID und SurgeonID erhalten:

<code>.05 (Shirts), .05 (Shoes), .05 (Dresses), .10 (Hats), .15 (Pants)
</code>

Ich versuche jedoch, es so aussehen zu lassen:

<code>.05 (Shirts, Shoes, Dresses), .10 (Hats), .15 (Pants)
</code>

Ich habe es ein paar Mal mit STUFF versucht, aber ich habe nie das Ergebnis bekommen, das ich suche.

Was veranlasst mich zu fragen, ob dies in MsSQL 2008 R2 überhaupt möglich ist? Wenn dies der Fall ist, wäre jede Hilfe bei der Erzielung des gewünschten Ergebnisses sehr dankbar.

Vielen Dank für Ihre Zeit und Energie,

Andrew

Deine Antwort

2   die antwort
3

Vielen Dank für das Wesentliche! So viel besser als Zähne zu ziehen, um Schema und Daten zu erhalten. :-) Wenn Sie dies in Ihre Hauptabfrage einfügen, sollten Sie die gewünschten Ergebnisse sehen (na ja, sehr nah - siehe unten).

<code>DECLARE @SalesRepID INT, @SurgeonID INT, @LocationID INT;
SELECT @SalesRepID = 2, @SurgeonID = 1, @LocationID = 1;

;WITH x AS 
(
  SELECT CommissionPercent, Categories = STUFF((SELECT ', ' 
      + tCat.Category FROM #tCategories AS tCat 
      INNER JOIN #tCommissions AS tCom 
      ON tCat.CategoryID = tCom.CategoryID
      WHERE tCom.CommissionPercent = com.CommissionPercent
      FOR XML PATH, TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') 
 FROM #tCommissions AS com
 WHERE SalesRepID = @SalesRepID
 AND SurgeonID = @SurgeonID
 AND LocationID = @LocationID
),
y AS
(
  SELECT s = RTRIM(CommissionPercent) + ' (' + Categories + ')' 
  FROM x GROUP BY CommissionPercent, Categories
)
SELECT Result = STUFF((SELECT ', ' + s FROM y 
  FOR XML PATH, TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');
</code>

Das Ergebnis weicht geringfügig von Ihrer Anfrage ab, Sie können dies jedoch beheben, indem Sie beim Abrufen von CommissionPercent die Zeichenfolgenformatierung anwenden.

<code>Result
--------------------------------------------------------
0.05 (Shirts, Shoes, Dresses), 0.10 (Hats), 0.15 (Pants)
</code>
@AaronBertrand sind auch damit einverstanden! und ja, meine Meinung könnte aufgrund meines .NET-Hintergrunds etwas subjektiv sein, aber denken Sie auch daran, dass es zu Kopfschmerzen kommen kann, wenn jemand, der nicht so geschickt ist, wie Sie es brauchen, um diese Abfrage zu optimieren! Chopin
Ich denke, das hängt von den Änderungen ab, die vorgenommen werden müssen. Chopin
Welche Lösung ist Ihrer Meinung nach aufwändiger zu warten, wenn jemand nur mäßig mit .NET und nur mäßig mit T-SQL vertraut ist? Sei ehrlich. Aaron Bertrand
Ich vermute, dass die Abfrage für jemanden mit OO / .NET-Hintergrund komplexer aussieht als eine CLR-Bereitstellung. Für mich schrieb ich diese Abfrage in ca. 8 Minuten. Ich würde weiterhin DLLs bereitstellen, wenn ich mich dafür entschieden hätte, dies in CLR zu tun. Es ist auch die gleiche Komplexität, wenn ich den Code auf einen neuen Server verschiebe (die Abfrage ist viel portabler) und wie gesagt, es ist noch schlimmer, wenn ich an der Abfrage eine Änderung vornehmen muss (wie ich es in getan hätte) in diesem Fall, weil ich die where-Klausel falsch positioniert habe). Ich sage nicht, dass CLR schlecht ist, aber ich denke, dass Ihre Ansicht, die komplexer / kontraintuitiver ist, ziemlich subjektiv ist. Aaron Bertrand
0

Ich bin zuvor auf ein ähnliches Problem gestoßen - und die einzige Möglichkeit, dieses Problem zu lösen (ohne Cursor zu verwenden), besteht darin, eine CLR-Aggregatfunktion zu erstellen. Hier ist ein Beispiel in C # (und in VB):http://technet.microsoft.com/en-us/library/ms131056(v=SQL.90).aspx

Ich glaube, es tut genau das, was Sie brauchen: Verkettung.

Wenn Sie Ihr Beispiel und die CLR kombinieren, um das zu erreichen, was Sie möchten, sieht die SQL folgendermaßen aus:

<code>SELECT
  c.CommissionPercent
  , dbo.MyAgg(cat.Category)
FROM #tCommissions AS c
JOIN #tCategories AS cat ON c.CategoryID = cat.CategoryID
group by c.CommissionPercent
</code>
Vielen Dank für Ihre schnelle Antwort. Obwohl dies nicht die Lösung war, die ich gewählt habe, danke ich Ihnen, dass Sie mir etwas Neues gezeigt haben. Ich war mir dieser Möglichkeit nicht einmal bewusst. ajtatum

Verwandte Fragen