将一列值拆分为多列值 [英] Split one column value into multiple column values
本文介绍了将一列值拆分为多列值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有订阅表，订阅号实际上是存储在一列中的一个值.我们有以下样本值
SC 5-1395-174-25PSC 1-2134-123-ABC C1-2SC 12-5245-1247-14&PSC 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** 解决方案
这是一个内联方法
示例
声明@YourTable 表 (SomeCol varchar(max))插入@YourTable 值('SC 5-1395-174-25P')，('SC 1-2134-123-ABC C1-2')，('SC 12-5245-1247-14&P'),('SC ABCD-2525-120')选择 B.*来自@YourTable A交叉申请 (选择 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) 乙退货
<块引用>编辑
1) 创建您的表格
CREATE TABLE MyNewPubTable (PUB_FORM_NUM NVARCHAR(50) , COL1 NVARCHAR(10) , COL2 NVARCHAR(10) , COL3 NVARCHAR(10) , COL4 NVARCHAR(10) , COL5 NVARCHAR(10) , COL106 NVAR) , COL7 NVARCHAR(10))2) 执行查询
声明@YourTable 表 (PUB_FORM_NUM varchar(max))插入@YourTable 值('SC 5-1395-174-25P')，('SC 1-2134-123-ABC C1-2')，('SC 12-5245-1247-14&P'),('SC ABCD-2525-120')插入到 MyNewPubTable选择 A.PUB_FORM_NUM,B.*来自@YourTable A交叉申请 (选择 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) 乙3) 查看结果
从 MyNewPubTable 中选择 *<块引用>
编辑 2
或者您可以动态创建表格
声明@YourTable 表 (PUB_FORM_NUM varchar(max))插入@YourTable 值('SC 5-1395-174-25P')，('SC 1-2134-123-ABC C1-2')，('SC 12-5245-1247-14&P'),('SC ABCD-2525-120')选择 A.PUB_FORM_NUM,B.*进入 MyNewPubTable来自@YourTable A交叉申请 (选择 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) 乙从 MyNewPubTable 中选择 *I have table of subscriptions and the subscription number is actually one value stored in one column. We have the following sample values
SC 5-1395-174-25P
SC 1-2134-123-ABC C1-2
SC 12-5245-1247-14&P
SC ABCD-2525-120
So we need to split this into individual columns. So the above four should be split the following way
**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**
解决方案
Here is an in-line approach
Example
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
Returns
EDIT
1) Create Your Table
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))
2) Execute the Query
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
3) Review the Results
Select * From MyNewPubTable
Edit 2
Or you can create the table on the fly
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')
Select A.PUB_FORM_NUM
,B.*
Into MyNewPubTable
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
Select * From MyNewPubTable
这篇关于将一列值拆分为多列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
