如何在 SQL Server 中將包含多個值的單一欄位值拆分為多個欄位？-mysql教程-PHP中文網

如何在 SQL Server 中將包含多個值的單一欄位值拆分為多個欄位？

Susan Sarandon

發布： 2024-12-23 19:13:09

原創

238 人瀏覽過

How can I split a single column value containing multiple values into multiple columns in SQL Server?

將單一欄位值拆分為多個欄位

資料管理中可能會出現將包含多個值的單一欄位拆分為單獨列的任務。例如，考慮一個具有名為「SubscriptionNumber」的欄位的表，該列將訂閱詳細資訊儲存在單一欄位中。為了使資料更易於管理和訪問，我們需要將各個值提取到多個列中。

使用交叉應用和 XML 的方法

實現此目的的一種方法是透過使用交叉應用和 XML 操作技術。假設我們有一個名為「Subscriptions」的表，其中包含「SubscriptionNumber」欄位。此列中的範例資料如下：

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

登入後複製

要將這些值拆分為單獨的列，我們可以使用以下查詢：

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&amp;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

登入後複製

此查詢使用交叉應用到為@YourTable 表中的每一行會產生一組行。在交叉應用中，XML 資料類型用於將 SubscriptionNumber 值轉換為 XML 片段。然後解析 XML 片段以提取各個值並將它們指派給 Pos1 到 Pos7 列。

使用動態 SQL 的替代方法

動態 SQL 方法也可以用來將 SubscriptionNumber 欄位分割為多列。這種方法需要建立一個暫存資料表來儲存分割值：

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&amp;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

登入後複製

動態 SQL 方法動態產生 SQL 語句來建立具有適當列名的暫存表。然後將分割後的值插入到這個臨時表中，可以根據需要從臨時表中選擇資料。

以上是如何在 SQL Server 中將包含多個值的單一欄位值拆分為多個欄位？的詳細內容。更多資訊請關注PHP中文網其他相關文章！