Wie kann ich einen einzelnen Spaltenwert, der mehrere Werte enthält, in SQL Server in mehrere Spalten aufteilen?

Aufteilen eines einzelnen Spaltenwerts in mehrere Spalten

Die Aufgabe, eine einzelne Spalte mit mehreren Werten in separate Spalten aufzuteilen, kann in der Datenverwaltung auftreten . Stellen Sie sich beispielsweise eine Tabelle mit einer Spalte namens „SubscriptionNumber“ vor, die Abonnementdetails in einem einzelnen Feld speichert. Um die Daten besser verwaltbar und zugänglich zu machen, müssen wir die einzelnen Werte in mehrere Spalten extrahieren.

Ansatz mit Cross Apply und XML

Eine Methode, dies zu erreichen, ist durch Verwendung einer Cross-Apply- und XML-Manipulationstechnik. Nehmen wir an, wir haben eine Tabelle namens „Subscriptions“ mit einer Spalte „SubscriptionNumber“. Die Beispieldaten in dieser Spalte lauten wie folgt:

SC 5-1395-174-25P 
SC 1-2134-123-ABC C1-2
SC 12-5245-1247-14&P
SC ABCD-2525-120

Um diese Werte in separate Spalten aufzuteilen, können wir die folgende Abfrage verwenden:

Declare @YourTable table (SubscriptionNumber varchar(max))
Insert Into @YourTable values
('SC 5-1395-174-25P'),
('SC 1-2134-123-ABC C1-2'),
('SC 12-5245-1247-14&P'),
('SC ABCD-2525-120')

Select B.*
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(replace(A.SubscriptionNumber,' ','-'),'-','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Diese Abfrage verwendet eine Kreuzanwendung Generieren Sie eine Reihe von Zeilen für jede Zeile in der @YourTable-Tabelle. Bei der Cross-Apply wird der XML-Datentyp verwendet, um den SubscriptionNumber-Wert in ein XML-Fragment zu konvertieren. Das XML-Fragment wird dann geparst, um die einzelnen Werte zu extrahieren und sie den Spalten Pos1 bis Pos7 zuzuordnen.

Alternativer Ansatz mit dynamischem SQL

Ein dynamischer SQL-Ansatz kann dies auch kann verwendet werden, um die Spalte „SubscriptionNumber“ in mehrere Spalten aufzuteilen. Dieser Ansatz erfordert die Erstellung einer temporären Tabelle zum Speichern der Teilungswerte:

Declare @YourTable table (SubscriptionNumber varchar(max))
Insert Into @YourTable values
('SC 5-1395-174-25P'),
('SC 1-2134-123-ABC C1-2'),
('SC 12-5245-1247-14&P'),
('SC ABCD-2525-120')

Declare @ColNames nvarchar(max) = ''
Declare @SQL nvarchar(max) = 'CREATE TABLE #TempTable (SubscriptionNumber varchar(max), '

-- Generate column names dynamically
Select @ColNames += ', Col' + CAST(Row_Number() OVER (ORDER BY (SELECT NULL)) AS nvarchar(10))
From (Select * From @YourTable) AS T
Cross Join (Select * From @YourTable) AS T2

-- Append column names to SQL statement
Set @SQL += @ColNames + ')'

-- Execute the dynamic SQL to create the temporary table
Exec (@SQL)

-- Insert split values into the temporary table
Insert Into #TempTable
Select SubscriptionNumber,
       ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
     , ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
     , ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
     , ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
     , ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
     , ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
     , ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
From @YourTable
Cross Apply (
                Select Cast('<x>' + replace((Select replace(replace(SubscriptionNumber,' ','-'),'-','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 

-- Select data from the temporary table
Select * From #TempTable

Der dynamische SQL-Ansatz generiert dynamisch die SQL-Anweisung, um eine temporäre Tabelle mit den entsprechenden Spaltennamen zu erstellen. Die Split-Werte werden dann in diese temporäre Tabelle eingefügt und die Daten können bei Bedarf aus der temporären Tabelle ausgewählt werden.

Das obige ist der detaillierte Inhalt vonWie kann ich einen einzelnen Spaltenwert, der mehrere Werte enthält, in SQL Server in mehrere Spalten aufteilen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!