Spalte in mehrere Spalten splitten

[adrian]

Hallo Datenbankforum

Folgende Situation:
In einer SQL-Tabelle sind in der Spalte ACCOUNTDISPLAYVALUE Einträge nach dem folgendem Muster vorhanden.


In einer View sollte ich nun die jeweils durch das Zeichen "|" getrennten Einzelwerte in 8 einzelnen Spalten haben
Ich habe es mit "PARSENAME" versucht, hatte aber damit keinen Erfolg. So weit ich mich erinnern kann, funktioniert dies sowieso nur mit 4 Spalten.

Hat jemand eine andere Idee, oder ist meine Anforderung mit SQL nicht umsetzbar.

Freundliche Grüsse
Adrian

 

[akretschmer]

also ich hab das mal kurz probiert...

postgres=# select * from adrian ;
         spalte          
-------------------------
 1|2|3|4|5|6|7|8
 11|12|13|14|15|16|17|18
 21|22|23|24|25|26|27|28
(3 rows)

postgres=# with foo as (select regexp_split_to_array(spalte, '\|') as a from adrian) select a[1], a[2], a[3],a[4],a[5],a[6],a[7],a[8] from foo;
 a  | a  | a  | a  | a  | a  | a  | a  
----+----+----+----+----+----+----+----
 1  | 2  | 3  | 4  | 5  | 6  | 7  | 8
 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18
 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28
(3 rows)

postgres=#

Den Spalten da noch eigene Spaltennamen zu vergeben (via Aliase) überlasse ich Dir zur Übung, allerdings ist das eine andere Datenbank ...

 

[bdittmar]

Hallo Datenbankforum

Folgende Situation:
In einer SQL-Tabelle sind in der Spalte ACCOUNTDISPLAYVALUE Einträge nach dem folgendem Muster vorhanden.
Anhang anzeigen 2159

In einer View sollte ich nun die jeweils durch das Zeichen "|" getrennten Einzelwerte in 8 einzelnen Spalten haben
Ich habe es mit "PARSENAME" versucht, hatte aber damit keinen Erfolg. So weit ich mich erinnern kann, funktioniert dies sowieso nur mit 4 Spalten.

Hat jemand eine andere Idee, oder ist meine Anforderung mit SQL nicht umsetzbar.

Freundliche Grüsse
Adrian

 

[bdittmar]

Hallo,
MSSQL STRING_SPLIT (Transact-SQL) - SQL Server

 

[dabadepdu]

Hallo,
MSSQL STRING_SPLIT (Transact-SQL) - SQL Server

Diese Funktion liefert Zeilen zurück, aber nicht Spalten, wie gesucht.
Das Beispiel von akretschmer macht das richtig und zeigt wie Eingabewerte (eine Spalte) und Ausgabewerte (viele Spalten) aussehen könnten.

 

[bdittmar]

Diese Funktion liefert Zeilen zurück, aber nicht Spalten, wie gesucht.
Das Beispiel von akretschmer macht das richtig und zeigt wie Eingabewerte (eine Spalte) und Ausgabewerte (viele Spalten) aussehen könnten.

 

[bdittmar]

Let's GOOGLE,
evtl. so:

Split Delimited String into Columns in SQL Server with PARSENAME

In this tip we look at how to parse or split SQL Server data from one column into multiple columns using the parsename and other T-SQL functions.

www.mssqltips.com

 

[adrian]

Hallo
Vielen dank für eure Rückmeldungen.
Die Lösung von akretschmer habe ich in SQL nicht umsetzen können. PARSENAME funktioniert nur bis 4 Spalten.

Ich konnte es aber in der Zwischenzeit lösen.
"STRING_SPLIT" liefert wie bereits erwähnt Zeilen, nicht Spalten. Also muss man es irgendwie hinkriegen die Zeilen in Spalten zu bringen.
Die Lösung heisst "PIVOT"

Auf Youtube gefunden:

Ich konnte diese Lösung sinngemäss für mich anpassen.
Ist zwar nicht sehr performant, aber es führt zum Ziel.

Viele Grüsse
Adrian

 

[dabadepdu]

Die Lösung von akretschmer habe ich in SQL nicht umsetzen können. PARSENAME funktioniert nur bis 4 Spalten.

Die Lösung ist ja auch nicht für MS SQL gedacht, nur als Idee wie es gehen könnte. PARSENAME ist wahrscheinlich eine ziemlich schlechte Idee, weil es gezielt dazu dienen soll, Object Names zu zerlegen und auch andere Probleme hat, nicht nur max 4 Elemente.

"STRING_SPLIT" liefert wie bereits erwähnt Zeilen, nicht Spalten. Also muss man es irgendwie hinkriegen die Zeilen in Spalten zu bringen.
Die Lösung heisst "PIVOT"

Ja, das ist eine ziemliche große Kurve als Workaround. Neue Zeilen produzieren, die dann wieder "mühsam" vereint werden müssen. Du hast selbst geschrieben, dass es nicht besonders schnell ist.

Ich setze kein MS SQL ein und habe mal etwas rumgeschaut, dabei ist mir eine XML Variante aufgefallen, die ohne diese Krücke mit dem Aufblasen zu Zeilen und anschließendn Luft raus lassen auskommt.
XML scheint mir aber unnötig komplex (Grammatik) und so habe ich nach JSON geschaut. Dafür habe ich Funktionen gefunden, die eine "flache" Lösung ermöglichen. Immer noch nicht elegant, aber ohne den riesigen Datenoverhead. Da es interne Funktionen der DB Engine sind, darf man hoffen, dass die Implementierung selbst auch ohne das Zeilenproblem deutlich schneller ist, als eine selbstgebaute T-SQL Lösung:

declare @col_str_val nvarchar(max) ='John|doe|26||abc';
declare @json nvarchar(max) =    '{"ar_val":["' + REPLACE(@col_str_val, '|', '","') + '"]}';

select @json,
      json_value(  @json, '$.ar_val[0]'),
      json_value(  @json, '$.ar_val[1]'),
      json_value(  @json, '$.ar_val[2]'),
      json_value(  @json, '$.ar_val[3]'),
      json_value(  @json, '$.ar_val[4]') as ok_finally_with_column_name;

Wie gesagt, ich kenne bzw. nutze T-SQL nicht, mglw. kann man das Beispiel also noch weiter verbessern. Bestimmt(!?) ist es schneller als eine String_Split oder Cross Apply -Pivot Lösung. Und leider nicht so elegant und verständlich wie die Postgres-Lösung von akretschmer.

als DB fiddle:

SQL Server 2022

| (No column name) | | :----------------| | Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) <br> Oct 8 2022 05:58:25 <br> Copyright (C) 2022 Microsoft Corporation<br> Express Edition (64-bit) on Windows Server 2019 Standard 10.0 \<X64> (Build 17763: ) (Hypervisor)<br> |

dbfiddle.uk

 

[akretschmer]

Die Lösung von akretschmer habe ich in SQL nicht umsetzen können. PARSENAME funktioniert nur bis 4 Spalten.

Dein Problem beginnt schon eher: warum speicherst Du überhaupt mehrere unabhängige Informationen in einem Feld?

 

[dabadepdu]

Dein Problem beginnt schon eher

Rhetorische Frage? Du weißt doch bestimmt sehr gut, dass man sich die Ausgangssituation selten aussuchen kann. Irgendwer muss irgendwann aufräumen, wenn die Sch.. zu sehr stinkt.
Ich gehe zumindest nicht davon aus, dass diese Probleme hier immer von irgendwelchen Glückspilzen stammt, die sich auf der grünen Wiese eine schöne, heile Welt bauen können.

 

[t-sql]

Rhetorische Frage? Du weißt doch bestimmt sehr gut, dass man sich die Ausgangssituation selten aussuchen kann. Irgendwer muss irgendwann aufräumen, wenn die Sch.. zu sehr stinkt.
Ich gehe zumindest nicht davon aus, dass diese Probleme hier immer von irgendwelchen Glückspilzen stammt, die sich auf der grünen Wiese eine schöne, heile Welt bauen können.

Nein keine rethorische Frage. Für akretschmer ist die Welt nur mit PostgreSQL eine heile Welt. Alles andere kennt er ja nicht.

 

[dabadepdu]

@t-sql
Naja, da vermischt Du jetzt 2 verschiedene Probleme.
Miese Ausgangslage in einem Projekt und die reine Postgres Perspektive. Das eine hat mit dem anderen nichts zu tun. Eine Lösung in einem anderen System gezeigt zu bekommen, kann einem dennoch eine gute Idee vermitteln, in welche Richtung es geht. Das tut er hier immer und zuverlässig.
Meine Frage drehte sich lediglich darum, warum er annimmt oder unterstellt, dass die Probleme hier stets am grünen Tisch entstehen.

Und darüber hinaus kann ich sehr gut verstehen, wenn man sich auf ein System beschränkt. Erstens aus Gründen der Fachkenntnis (bei akretschmer würde ich es eher Intimkenntnis nennen, ähnlich wie bei mir, das Prinzip ist ja fast immer identisch, nur die Auswirkungen fehlender oder schlechter Implementierungen oft sehr dramatisch) und zweitens, warum sollte man sich (in seiner Freizeit) um ein teures, kommerzielles System kümmern, an dem der Hersteller sich dumm und dämlich verdient und dann auch noch nach Lösungen (Workarounds) suchen, die in anderen Systemen besser und standardkonform gelöst sind? Von Dir bspw. kenne ich es auch nicht, dass Du Dich um andere Systeme kümmerst, als MS SQL bzw. um die Probleme der Leute, die damit arbeiten (müssen).

Insofern verstehe ich Deinen Einwand noch weniger, als den von akretschmer.

Ich freue mich jedenfalls in einem Forum immer über Hilfe, selbst wenn es "nur" konzeptionell ist, keine plug and play Lösung für mein konkretes Problem oder selbst nur der Hinweis, dass mein Anliegen einfach nicht umsetzbar ist. All das erspart mir oft viel Recherche und Kopfzerbrechen / Zeit.

 

[ukulele]

Die JSON-Variante kenne ich noch nicht, kam genauso wie STRING_SPLIT erst mit SQL 2016. XML gibt es schon länger, daher wäre das auch mein erster Impuls gewesen.

Ein Ansatz mit PostgreSQL hat für den Interessierten sicherlich einen Wert aber in diesem Fall ist das nicht 1:1 übertragbar. Und zur Verteidigung von @t-sql : Er hat schon recht das @akretschmer auf jede Frage eine Lösungen immer nur als PG-Variante liefert und wir kennen alle seine Einstellung und die Message die er transportiert.

 

[t-sql]

Eins noch zur Ausgangslage: So eine Transformierung gehört in die Anwendung und hat inner Datenbank eigentlich nix verloren.