Question

SQLsever management 一列拆分为多列

比如图中第一列，现在是五个两位数数字中间有加号，空格，再两个两位数
我现在想把这一列拆分为7列应该怎么做？
比如，”04+06+22+25+32 07+11“，我想要拆分为7列，“04” “06” “22” “25” “32” “07” “11”
求大神指导，小白一枚！

Answer 1

假设原来的列名为column1
Select
Substring(Column1,1,2) as Num1,
Substring(Column1,4,2) as Num2,
Substring(Column1,7,2) as Num3,
Substring(Column1,10,2) as Num4,
Substring(Column1,13,2) as Num5,
Substring(Column1,16,2) as Num6,
Substring(Column1,19,2) as Num7
From
table

追问

谢谢！的确可以！
但是比如说原来还有colum2，colum3，我想把这些列的内容加在num1，num2，。。。，num7之后，总的保存成一个新的表，应该是什么指令？求指导！

追答

1、先建个新表：

Create table A(
Col1 varchar(10) ,
Col2 varchar(10) ,
Col3 varchar(10) ,
Col4 varchar(10) ,
Col5 varchar(10) ,
Col6 varchar(10) ,
Col7 varchar(10) ,
Col8 varchar(10) ,
Col9 varchar(10)

)
2、
Insert into A (Col1,Col2,...Col9) values (
Select
Substring(Column1,1,2) as Num1,
Substring(Column1,4,2) as Num2,
Substring(Column1,7,2) as Num3,
Substring(Column1,10,2) as Num4,
Substring(Column1,13,2) as Num5,
Substring(Column1,16,2) as Num6,
Substring(Column1,19,2) as Num7,
Column2,

Column3

From
table
)