Spaltenwerte als kommaseparierte Liste zurückgeben Teil 2

By Frank Kalis

Posted on Sep 28, 2007 von in SQL Server

In diesem Beitrag haben wir uns mit der Rückgabe von Werten einer Spalte als separierte Liste beschäftigt. Vielfach sieht man aber auch die Anforderung, diese Liste auf der Basis eines bestimmten Schlüsselwertes zu generieren.

Zum Beispiel eine Liste, die alle Kunden-Nr und die dazugehörigen Bestellungen ausweist. Die XML Funktionalitäten des SQL Servers ab Version 2005 in Zusammenarbeit mit CROSS APPLY machen durchaus eine leichte Übung:

SELECT CustomerID, LEFT(MyCommaSeparatedList
       , LEN(MyCommaSeparatedList)-1) AS MyCommaSeparatedList
  FROM Northwind.dbo.Customers c 
  CROSS APPLY
  (SELECT CAST(OrderID AS NVARCHAR(10)) + ','
    FROM Northwind.dbo.Orders o
   WHERE o.CustomerID = c.CustomerID
   ORDER BY o.OrderID
     FOR XML PATH('')) AS x(MyCommaSeparatedList)

CustomerID MyCommaSeparatedList
---------- ------------------------------------------
ALFKI      10643,10692,10702,10835,10952,11011
ANATR      10308,10625,10759,10926
ANTON      10365,10507,10535,10573,10677,10682,10856

Ein weiterer angenehmer "Nebeneffekt" ist, daß man dadurch nicht mehr der 8000 Zeichen Grenze des VARCHAR Datentypen unterliegt, sondern der Grenze des XML Datentypen unterliegt, die bei ca. 2 GB liegt.

Dieser Eintrag wurde eingetragen von und ist abgelegt unter SQL Server. Tags: , , , ,

7 Kommentare

Benutzerwertungen
5 Stern:
 
(2)
4 Stern:
 
(0)
3 Stern:
 
(0)
2 Stern:
 
(0)
1 Stern:
 
(0)
2 Bewertungen
Durschn. Benutzerwertung:
5.0 stars
(5.0)

Kommentar von: Armin

Armin
5 stars
Zunächst einmal vielen Dank für diesen Beitrag. Nun zu meinem Problem. Ich möchte EMail Adressen zusammen fassen. Dazu habe ich den o. g. für meine Zwecke etas anpassen müssen. SELECT t.ABTEILUNG, t.STEP, LEFT(MYCOMMASEPARATEDLIST, Len(MYCOMMASEPARATEDLIST) - 1) AS MYCOMMASEPARATEDLIST FROM myDB.dbo.qry_verteilerkontakte AS t CROSS APPLY (SELECT Cast(EMAIL AS VARCHAR(255)) + ';' FROM myDB.dbo.qry_verteilerkontakte AS v WHERE t.ID1 = v.ID1 AND t.ID2 = v.ID2 ORDER BY v.ABTEILUNG FOR XML PATH('')) AS x(MYCOMMASEPARATEDLIST) Nun bekomme ich aber bei der Ausführung eine Fehlermeldung. Msg 537, Level 16, State 5, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function. Vielen Dank im Voraus und Grüße aus HH Armin Wo hakt es denn da bei mir?
30.08.12 @ 17:32
Kann es sein, dass MYCOMMASEPARATEDLIST bei dir manchmal nur ein leerer String ist? Falls ja, knallt das LEFT weil dann Len(MYCOMMASEPARATEDLIST) - 1 = -1 ist und den Fehler verursacht. Seit ich damals den Beitrag geschrieben habe, habe ich mir angewöhnt, den String anders zu verketten, indem ich den Separator voranstelle und dann die anderen Werte anhänge. Das gibt mir dann die Möglichkeit, anstelle der Verbindung der zwei Funktionen LEFT() & LEN() nur noch SUBSTRING() ausführen zu müssen, um das gleiche zu erzielen. Also, in deinem Fall so etwa:
SELECT
    t.ABTEILUNG,
    t.STEP,
    SUBSTRING(MYCOMMASEPARATEDLIST, 2, 8000) AS MYCOMMASEPARATEDLIST
FROM
    myDB.dbo.qry_verteilerkontakte AS t
    CROSS APPLY (SELECT
                    ';' + CAST(EMAIL AS varchar(255))
                 FROM
                    myDB.dbo.qry_verteilerkontakte AS v
                 WHERE
                    t.ID1 = v.ID1 AND
                    t.ID2 = v.ID2
                 ORDER BY
                    v.ABTEILUNG
    FOR
                 XML PATH('')) AS x (MYCOMMASEPARATEDLIST)
03.09.12 @ 07:06

Kommentar von: Armin

Armin
5 stars
Moin Frank, klappt nun prima. Dankeschön. :o) Nun stehe ich aber auch schon vor dem nächsten Problem. Ich benutze die Abfrage zum einen um einen "An" Verteiler zu erstellen und zum anderen um einen "Cc" Verteiler zu erstellen. Im Cc sollen immer alle die drin stehen die bereits vorher im An gestanden haben. Beispiel: Step1 An: Name1; Name2; ... Cc: Nicht definiert! Step2 An: Name3 Cc: Empfänger aus Step1 Step3 An: Name4 Cc: Empfänger aus Step1 und Step2 Step4 An: Name5 Cc: Empfänger aus Step1, Step2 und Step3 Step1 und 2 funktionieren noch aber spätestens beim dritten bekomme ich Schwierigkeiten da werden wieder mehrere Zeilen generiert. Kann man das auch irgendwie verwurschteln?
03.09.12 @ 11:02
Ich bin mir jetzt nicht sicher, ob ich dein Problem wirklich verstehe. Du versuchst über eine Abfrage die Schritte 1 - 4 abzuarbeiten? Da würde ich mal in Richtung rekursive CTE googlen.
04.09.12 @ 07:17

Kommentar von: Armin

Armin
Vielleicht habe ich mit "Step" das falsche Wort gewählt. Es geht bei mir darum eine DB zum Eskalieren von bestimmten themen zu basteln. In der Eskalationsstufe 1 gibt es nur "An" Adressaten. In der Eskalationsstufe 2 soll es andere "An" Adressaten plus die "An" Adressaten aus Stufe 1 im "Cc" geben usw... Werde heute mal nach den Rekursiven Abfragen schauen. Mal schauen ob ich dahinter blicke.
05.09.12 @ 09:55
Ja, dann habe ich dich tatsächlich falsch verstanden. Bei solch einer Aufgabenstellung würde ich sowieso dann darüber nachdenken, die diversen Eskalationsstufen in Tabellen abzubilden, um nachzuvollziehen, wann welches Thema welche Stufe erreicht hat und was aufgrund dessen veranlasst wurde und wer wie benachrichtigt werden soll. Ausserdem würde ich mir Konfigurationstabellen anlegen, wo definiert wird, für welches Thema in welcher Stufe welche Personen eine Mail als Empfänger oder CC: erhalten sollen und dies auch in der Datenbank klar separieren, vielleicht sogar in separaten Tabellen ablegen. Damit reduziert sich der Aufwand dann später auf das Verketten der einzelnen Empfänger in Abhängigkeit von der Eskalationsstufe und du musst nicht erst ermitteln, ob jemand schon vorher benachrichtigt wurde und jetzt auf cc: erscheinen soll. Ein netter Nebeneffekt ist ausserdem, dass alles datengesteuert abläuft und die Pflege dafür dem Anwender übertragen wird. :-)
07.09.12 @ 07:16

Kommentar von: Armin

Armin
So! Aus dem Urlaub zurück und fit für neue Herausforderungen. Deswegen die späte Meldung. Sorry! Dein Vorschlag hört sich interressant an. Aber erzeuge ich dann nicht redundante Daten? Ich habe im Moment auch noch so gar keine Vorstellung wie ich das Umsetzen soll und wie genau Du Dir das mit den Konfigurationstabellen vorgestellt hast. Ich kann ja mal einen groben Abriss über den Aufbau meiner DB geben obgleich das wohl das Off-Topic geht. Ichabe also eine Tabelle mit Kontakten, eine mit den Abteilungen für die sie tätig sein können, eine Hilfstabelle die Kontakte und Abteilungen m:n verbindet, eine mit den Eskalationsstufen, eine weitere HT die die Eskalationsstufen mit der HT KontakteAbteilungen m:n verbindet und natürlich die Tabelle in der alle Themen aufgelistet sind mit Ihrer jeweiligen Eskalationsstufe als Markierung. Dachte eigentlich das wäre schon ziemlich gut aber besser geht ja immer. vielleicht kann man das ja in einem anderen Themenbereich weiterdiskutieren wenn es hier nicht der richtige Platz ist. gerne auch per Mail..
24.09.12 @ 13:18


Formular wird geladen...