SQL Server で、複数の値を含む 1 つの列の値を複数の列に分割するにはどうすればよいですか?

単一の列の値を複数の列に分割する

複数の値を含む単一の列を個別の列に分割するタスクは、データ管理で発生する可能性があります。 。たとえば、サブスクリプションの詳細を 1 つのフィールドに格納する「SubscriptionNumber」という名前の列を持つテーブルを考えてみましょう。データをより管理しやすくアクセスしやすくするには、個々の値を複数の列に抽出する必要があります。

クロス適用と XML を使用したアプローチ

これを実現する 1 つの方法は次のとおりです。クロス適用および XML 操作技術を使用します。 「SubscriptionNumber」列を持つ「Subscriptions」というテーブルがあると仮定します。この列のサンプル データは次のとおりです。

SC 5-1395-174-25P 
SC 1-2134-123-ABC C1-2
SC 12-5245-1247-14&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&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&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 で、複数の値を含む 1 つの列の値を複数の列に分割するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。