Reprinted from: http://www.maomao365.com/?p=10278
Summary:
The following describes the method of separating single column data combined with commas in sqlserver into multiple columns of data
Experimental environment: sql server 2012
Implementation ideas:
1. Replace comma-separated data with xml data
span>
2. Use xml read function to read related data
Example:
Example of splitting a string containing a comma into multiple columns
create table [maomao365.com]
(info varchar(200))
go
---Generate basic data
insert into [maomao365.com]
(info)values('1,2,3,4,5' ),
('6,7,8,9< span style="color: #ff0000;">'),('10, 11,23,24,45,67')
go
---Use cte expression to convert to xml format and convert to corresponding column information
;
with tmp as (
select '' + replace(info,',< span style="color: #ff0000;">',' ') + '' as newInfo from [maomao365.com]
)
---Generate xml information
select
convert(xml,newInfo).value('tmp[1]','int') as< /span> field1,
convert(xml,newInfo).value(' tmp[2]', 'int') as field2,
convert(xml,newInfo).value(' tmp[3]', 'int') as field3,
convert(xml,newInfo).value(' tmp[4]', 'int') as field4,
convert(xml,newInfo).value(' tmp[5]', 'int') as field5,
convert(xml,newInfo).value(' tmp[6]', 'int') as field6
from tmp
go
truncate table [maomao365.com]
drop table [maomao365.com]
< p>
< p>
create table [maomao365.com]
(info varchar(200))
go
---Generate basic data
insert into [maomao365.com]
(info)values('1,2,3,4,5' ),
('6,7,8,9< span style="color: #ff0000;">'),('10, 11,23,24,45,67')
go
---Use cte expression to convert to xml format and convert to corresponding column information
;
with tmp as (
select '' + replace(info,',< span style="color: #ff0000;">',' ') + '' as newInfo from [maomao365.com]
)
---Generate xml information
select
convert(xml,newInfo).value('tmp[1]','int') as< /span> field1,
convert(xml,newInfo).value(' tmp[2]', 'int') as field2,
convert(xml,newInfo).value(' tmp[3]', 'int') as field3,
convert(xml,newInfo).value(' tmp[4]', 'int') as field4,
convert(xml,newInfo).value(' tmp[5]', 'int') as field5,
convert(xml,newInfo).value(' tmp[6]', 'int') as field6
from tmp
go
truncate table [maomao365.com]
drop table [maomao365.com]
< p>