SQL MSSQL: Kommaseparierten Varchar auf mehrere Zeilen aufteilen

[AgiOli]

Hallo zusammen,

ich steh gerade auf dem Schlauch und habe keine Idee, wie ich weiter kommen könnte.

Ich habe eine Tabelle in der Art
[table="width: 500, class: grid"]
[tr]
[td]ID[/td]
[td]Textwert[/td]
[/tr]
[tr]
[td]1[/td]
[td]Text1,Text2[/td]
[/tr]
[tr]
[td]2[/td]
[td]Text3,Text4,Text5[/td]
[/tr]
[/table]

Mittels einem Select möchte ich jetzt gern die Einträge in der Spalte Textwert an den Kommas aufteilen und so für jeden Eintrag eine Zeile zurückbekommen, als das Ergebnis sollte so aussehen:
[table="width: 500, class:grid"]
[tr]
[td]ID[/td]
[td]Text[/td]
[/tr]
[tr]
[td]1[/td]
[td]Text1[/td]
[/tr]
[tr]
[td]1[/td]
[td]Text2[/td]
[/tr]
[tr]
[td]2[/td]
[td]Text3[/td]
[/tr]
[tr]
[td]2[/td]
[td]Text4[/td]
[/tr]
[tr]
[td]2[/td]
[td]Text5[/td]
[/tr]
[/table]

Ich hoffe, ich konnte halbwegs versändlich machen, was mein Problem ist.

 

[SaxnPaule]

Evtl. könnte das helfen: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

 

[RalphS]

Schau mal hier.

Und überdenke gegebenenfalls Dein Datenbankschema. Spalten sollten einwertig sein. Sonst kann man auch Excel nehmen.

 

[rg88]

Was machst du mit den Daten aus dem Select im Anschluß? Wäre es nicht einfacher den im nächsten Programm erst codetechnisch auseinanderzupflücken. Dürfte um einiges einfacher sein und man kann evtl. Besonderheiten damit abdecken.

 

[AgiOli]

Danke für den Tipp.
Leider handelt es sich um einen MS SQL-Server 2008 R2
Daher ist String_Split leider keine Option.

Am Design kann ich leider nichts ändern - es handelt sich um die Datenbank unseres CRMs, auf dem ich eine Auswertung machen soll und die Werte stehen halt genau so drin.

 

[SaxnPaule]

Womit machst du die Auswertung?

 

[RalphS]

... ich frag lieber nicht, was dieses System fürn Mist macht. ^_^

Schau mal in Richtung CHARINDEX und SUBSTRING. Da müßte man dann allerdings eine Funktion drumherum bauen, mit Signatur udf_funktion(int, varchar) returns table(text_id int, string_pos int, substring_text varchar).

text_id wäre die ID des ursprünglichen Strings, string_pos die Position der ent-komma-fizierten Substrings innerhalb des ursprünglichen Strings und substring_text dieser Substring im Klartext.

Dann kann man das ursprüngliche Textfeld mit den Kommata drin übergeben und weiß hinterher immer noch, welcher Originalstring zu welchem aufgetrennten String gehört.

 

[SaxnPaule]

Diese Funktion tut genau was du willst. Du musst nur noch über die einzelnen Zeilen iterieren: https://stackoverflow.com/questions/21428612/splitting-the-string-in-sql-server

 

[AgiOli]

Super - Vielen Dank, die Tipps helfen mir auf alle Fälle mal sehr weiter.

Die Weiterverarbeitung bzw. Auswertung erfolgt mit Crystal Reports.

 

[Webentwickler]

Du solltest dich dringend mit dem Thema Normalisierung beschäftigen.

Dein oben vorgestelltes Datenmodell ist nicht atomar (1NF), wodurch insbesondere Listen und Wiederholungen verboten sind.

 

[rg88]

Kommt aber auch auf die DB drauf an. Für relationale Datenbanken wie hier stimmt das, für NoSQL-Datenbanken ist das eine andere Geschichte.

 

[AgiOli]

Ich habe den Stackoverflow-Tipp von SaxnPaule genutzt und umgeschrieben.
It der Tabell die ich oben skizziert habe mittels

select id, split.xmlTable.value('.', 'varchar(255)') as xmlValue
from (
       select id, cast(('<w>' + replace(Textvalue, ',', '</w><w>') + '</w>') as xml) as xmlValue from test
     ) as xmlTable
cross apply xmlValue.nodes ('/w') as split(xmlTable);

funktioniert es wie gewollt.

Zum Thema Datenbankdesign, Normierung u.Ä.:
Bei den Angaben, die in dem besagten Feld kommasepariert stehen, handelt es sich um Daten, die im CRM manuell eingegeben sind und auch nicht zur automatischen Weiterverarbeitung gedacht sind. Jetzt kam halt einer auf die Idee, diese Angaben auswerten zu wollen und mir kommt diese ehrenvolle Aufgabe zu.

 

[RalphS]

Je nachdem, wieviel Du da zu sagen hast, solltest Du ggf ansprechen, daß es so nicht geht.

Eben aus dem von Dir genannten Grund. Irgendwann will man das ja vielleicht doch mal auswerten. Dann muß man sich - im Vorhinein -- die Mühe machen, die Benutzerdaten sinnvoll auf Tabellenspalten zu biegen.

Das hat nichts mit Automatismen zu tun, sondern mit der Datenbanklogik. Wie Du sicherlich selbst merkst: mit unstrukturierten Daten kann man auch als Mensch nichts anfangen und Fehleingaben sind buchstäblich vorprogrammiert.

Aber das nur nebenher. An der bestehenden Situation ändert das natürlich nichts mehr.

 

[ayngush]

So einen Mist kenne ich als alter "Datenbänker / CRM-/DMS-/ERP-Verwurschtler / DevOp / mach das Geht-Typ" nur zur Genüge

Da trifft die harte Realität auf die trockene Theorie. Die 3NF ist das erstrebenswerte Ziel, damit man auch vernünftige Auswertungen und Aussagen treffen kann. Dumm nur, dass die Geschäftsprozesstypen, falls sie denn BPMN / EPC können, mit ERM nichts anfangen können und Kardinalitäten für irgendwas esoterisches aus der Kirche halten.

Im CRM Gibt es garantiert ein "Notiz"-Feld und irgend ein Schlaumeier von "mal eben ein Geschäftsprozess basteln"-Typ im Unternehmen, alternativ die mächtige Schatten-IT, kam auf die glorreiche Idee dort Dinge einzutragen, für die im CRM keine Logik / kein Platz vorhanden ist, die man aber gerne trotz Datensparsamkeitsgebot "aufheben möchte".
Das ist leider Alltag in tausenden von Unternehmen rund um den Planeten zu jedem beliebigen Zeitpunkt.

Lösung:
1. Das CRM anpassen alternativ: Ein anpassbares oder passendes CRM besorgen und migrieren.
2. Die Geschäftsprozesse überhaupt erst mal ordentlich dokumentieren und definieren und dabei dann optimieren: "Keine Daten in "Notizfelder" eingeben, die keine "Notizen" sind".
3. Die Sachbearbeiter*innen schulen.

Bis dahin helfen die oben genannten Workarounds über String-Splitting-Funktionen. Zur Not bastelt man sich einen "Parser" in einer beliebigen Programmiersprache für das Problem. Oftmals ist das auch einfacher als am CRM herum zu wurschteln und zu versuchen Sachbearbeitern ein neues Eingabeverhalten beizubiegen...

"Welcome to the real World" - Morpheus

 

[Webentwickler]

Im CRM Gibt es garantiert ein "Notiz"-Feld und irgend ein Schlaumeier von "mal eben ein Geschäftsprozess basteln"-Typ im Unternehmen, alternativ die mächtige Schatten-IT, kam auf die glorreiche Idee dort Dinge einzutragen, für die im CRM keine Logik / kein Platz vorhanden ist, die man aber gerne trotz Datensparsamkeitsgebot "aufheben möchte".
Das ist leider Alltag in tausenden von Unternehmen rund um den Planeten zu jedem beliebigen Zeitpunkt.

Jupp, das geht sogar so weit, dass sich Kunden in kooperativen Meetings mit Händen und Füßen gegen vernüftige Datenmodellierung wehren, weil irgendwelche Sachbearbeiter dann ihre 20 Jahre währenden, teils auf Excel oder Eigenentwicklungen basierenden Prozesse umstellen müssten und wirklich mit Gewalt versuchen, diese Allzweckfreitextfelder durchzusetzen.

 

[AgiOli]

Du sprichst mir so aus der Seele
Mit der Zeit und Erfahrung lernt man, die schöne Theorie an die Gegebenheiten der Paris anzupassen.

Lösung:
1. Das CRM anpassen alternativ: Ein anpassbares oder passendes CRM besorgen und migrieren.
2. Die Geschäftsprozesse überhaupt erst mal ordentlich dokumentieren und definieren und dabei dann optimieren: "Keine Daten in "Notizfelder" eingeben, die keine "Notizen" sind".
3. Die Sachbearbeiter*innen schulen.

Zum Glück ist bei mir im Betrieb Besserung in Sicht. Momentan werden im Rahmen eines ERP-Auswahlprozesses alle Prozesse genau aufgenommen, verschlankt, ausgemistet und modernisiert. Und die alten Syteme werden abgelöst werden und die IT hat volle Mitsprache- und Entscheidungsrechte.
Es kann nur besser werden.