Wie erweitere ich kommagetrennte Werte in separate Zeilen mit SQL Server 2005?

Question

Ich habe eine Tabelle, die wie folgt aussieht:

ProductId, Color
"1", "red, blue, green"
"2", null
"3", "purple, green"

Und ich möchte sie auf dieses Thema ausweiten:

ProductId, Color
1, red
1, blue
1, green
2, null
3, purple
3, green

Wie lässt sich dies am einfachsten bewerkstelligen? Ist es ohne eine Schleife in einem Proc möglich?

Answer 1

Ich würde dafür eine tabellendefinierte CLR-Funktion erstellen:

http://msdn.microsoft.com/en-us/library/ms254508(VS.80).aspx

Der Grund dafür ist, dass der CLR-Code viel besser in der Lage ist, die Zeichenketten zu zerlegen (Rechenarbeit) und diese Informationen als Set zurückgeben kann, was die eigentliche Stärke von SQL Server ist (Set-Management).

Die CLR-Funktion würde eine Reihe von Datensätzen basierend auf den geparsten Werten (und dem eingegebenen ID-Wert) zurückgeben.

Sie würden dann ein CROSS APPLY auf jedes Element in Ihrer Tabelle anwenden.

Answer 2

Konvertieren Sie einfach Ihre Spalten in xml und fragen Sie sie ab. Hier ist ein Beispiel.

select 
    a.value('.', 'varchar(42)') c
from (select cast('<r><a>' + replace(@CSV, ',', '</a><a>') + '</a></r>' as xml) x) t1
cross apply x.nodes('//r/a') t2(a)