Spaltenwerte als kommaseparierte Liste zurückgeben Teil 2

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.

  • 5 stars
    Armin
    Kommentar von: Armin
    30.08.12 @ 17:32:45

    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?

  • Kommentar von: Frank Kalis
    03.09.12 @ 07:06:00

    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)

  • 5 stars
    Armin
    Kommentar von: Armin
    03.09.12 @ 11:02:57

    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?

  • Kommentar von: Frank Kalis
    04.09.12 @ 07:17:03

    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.

  • Armin
    Kommentar von: Armin
    05.09.12 @ 09:55:36

    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.

  • Kommentar von: Frank Kalis
    07.09.12 @ 07:16:41

    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. :-)

  • Armin
    Kommentar von: Armin
    24.09.12 @ 13:18:34

    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..

Einen Kommentar hinterlassen

You must be logged in to leave a comment. Log in now!

If you have no account yet, you can register now...
(It only takes a few seconds!)