MSSQL SQL Server converts a comma-separated one column data to multiple column data.

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>Share a picture

< 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>

WordPress database error: [Table 'yf99682.wp_s6mz6tyggq_comments' doesn't exist]
SELECT SQL_CALC_FOUND_ROWS wp_s6mz6tyggq_comments.comment_ID FROM wp_s6mz6tyggq_comments WHERE ( comment_approved = '1' ) AND comment_post_ID = 283 ORDER BY wp_s6mz6tyggq_comments.comment_date_gmt ASC, wp_s6mz6tyggq_comments.comment_ID ASC

Leave a Comment

Your email address will not be published.