SQL を使用して、スペース区切りの値を含む単一のデータベース列を複数の列に分割するにはどうすればよいですか?

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

問題の説明

単一の列に格納されたサブスクリプション番号を含むデータベース テーブルについて考えてみましょう。各数値は特定の形式に従います:

• SC の後に 1 つ以上のスペース区切りの値が続きます。

例:

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

目的は、この単一列のデータを複数の列に分割し、構造化されたデータを生成することです。表現:

**Col1**   **Col2**   **Col3**   **Col4**  **Col5**  **Col6**   **Col7**
**SC**      **5**     **1395**   **174**   **25P** 
**SC**      **1**     **2134**   **123**   **ABC**   **C1**      **2**
**SC**      **12**    **5245**   **1247**  **14&P**
**SC**      **ABCD**  **2525**   **120**

解決策

これを実現するには、インライン クエリで XML と CROSS APPLY の組み合わせを利用できます:

Declare @YourTable table (SomeCol 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.SomeCol,' ','-'),'-','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

代替解決策

より完全な実装のために、このクエリを分割を保存する新しいテーブルの作成に統合できます。値:

CREATE TABLE MyNewPubTable (PUB_FORM_NUM NVARCHAR(50) , COL1 NVARCHAR(10) , COL2 NVARCHAR(10) , COL3 NVARCHAR(10) , COL4 NVARCHAR(10) , COL5 NVARCHAR(10) , COL6 NVARCHAR(10) , COL7 NVARCHAR(10)) 

Declare @YourTable table (PUB_FORM_NUM 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')

Insert Into MyNewPubTable
Select A.PUB_FORM_NUM
      ,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.PUB_FORM_NUM,' ','-'),'-','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

このクエリを実行すると、分割された値を含む MyNewPubTable が作成されます。

以上がSQL を使用して、スペース区切りの値を含む単一のデータベース列を複数の列に分割するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。