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

Schauen Sie sich diese Funktion an. Ich habe ähnliche Tricks angewandt, um Daten in Oracle aufzuteilen und zu transponieren. Schleife über die Daten und Einfügen der entschlüsselten Werte in eine temporäre Tabelle. Der Clou dabei ist, dass MS dies im laufenden Betrieb zulässt, während Oracle eine explizite temporäre Tabelle erfordert.

MS SQL Split-Funktion
Bessere Splitfunktion

Bearbeitung durch den Autor: Das hat hervorragend funktioniert. Der endgültige Code sah wie folgt aus (nach der Erstellung der Split-Funktion):

select pv.productid, colortable.items as color
from product p 
    cross apply split(p.color, ',') as colortable

Answer 2

Basierend auf Ihren Tabellen:

create table test_table
(
     ProductId  int
    ,Color      varchar(100)
)

insert into test_table values (1, 'red, blue, green')
insert into test_table values (2, null)
insert into test_table values (3, 'purple, green')

Erstellen Sie eine neue Tabelle wie diese:

CREATE TABLE Numbers
(
    Number  int   not null primary key
)

die Zeilen mit den Werten 1 bis 8000 oder so enthält.

wird das Gewünschte zurückgeben:

EDIT
Hier ist eine viel bessere Anfrage, leicht abgewandelt von der großartigen Antwort von @Christopher Klein:

Ich habe "LTRIM()" hinzugefügt, damit die Leerzeichen in der Farbliste richtig behandelt werden: "rot, blau, grün". Seine Lösung erfordert keine Leerzeichen "rot, blau, grün". Außerdem ziehe ich es vor, meine eigene Zahlentabelle und nicht master.dbo.spt_values zu verwenden, da dies auch das Entfernen einer abgeleiteten Tabelle ermöglicht.

SELECT
    ProductId, LEFT(PartialColor, CHARINDEX(',', PartialColor + ',')-1) as SplitColor
    FROM (SELECT 
              t.ProductId, LTRIM(SUBSTRING(t.Color, n.Number, 200)) AS PartialColor
              FROM test_table             t
                  LEFT OUTER JOIN Numbers n ON n.Number<=LEN(t.Color) AND SUBSTRING(',' + t.Color, n.Number, 1) = ','
         ) t

EDIT ENDE

SELECT
    ProductId, Color --,number
    FROM (SELECT
              ProductId
                  ,CASE
                       WHEN LEN(List2)>0 THEN LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(',', List2, number+1)-number - 1)))
                       ELSE NULL
                   END AS Color
                  ,Number
              FROM (
                       SELECT ProductId,',' + Color + ',' AS List2
                           FROM test_table
                   ) AS dt
                  LEFT OUTER JOIN Numbers n ON (n.Number < LEN(dt.List2)) OR (n.Number=1 AND dt.List2 IS NULL)
              WHERE SUBSTRING(List2, number, 1) = ',' OR List2 IS NULL
         ) dt2
    ORDER BY ProductId, Number, Color

Hier ist meine Ergebnisliste:

ProductId   Color
----------- --------------
1           red
1           blue
1           green
2           NULL
3           purple
3           green

(6 row(s) affected)

was die gleiche Reihenfolge ist, die Sie wollen...

Answer 3

Sie können dies ausprobieren, es sind keine zusätzlichen Funktionen erforderlich:

declare @t table (col1 varchar(10), col2 varchar(200))
insert @t
          select '1', 'red,blue,green'
union all select '2', NULL
union all select '3', 'green,purple'

select col1, left(d, charindex(',', d + ',')-1) as e from (
    select \*, substring(col2, number, 200) as d from @t col1 left join
        (select distinct number from master.dbo.spt\_values where number between 1 and 200) col2
        on substring(',' + col2, number, 1) = ',') t

Answer 4

Ich habe diese Frage 10 Jahre nach der Veröffentlichung gestellt. SQL Server 2016 hat die Funktion STRING_SPLIT hinzugefügt. Durch die Verwendung, kann dies als unten geschrieben werden.

declare @product table
(
    ProductId int,
    Color     varchar(max)
);
insert into @product values (1, 'red, blue, green');
insert into @product values (2, null);
insert into @product values (3, 'purple, green');

select
    p.ProductId as ProductId,
    ltrim(split_table.value) as Color
from @product p
outer apply string_split(p.Color, ',') as split_table;

Answer 5

Reparieren Sie Ihre Datenbank, wenn das möglich ist. Durch Kommata getrennte Listen in Datenbankzellen weisen in 99 % der Fälle oder mehr auf ein fehlerhaftes Schema hin.