34,838
社区成员




select a.id,
vc=substring(a.vc+',',b.number,charindex(',',a.vc+',',b.number)-b.number)
from #t a inner join master..spt_values b on b.type = 'p'
where b.number = charindex(',',','+a.vc,b.number)
CREATE TABLE MyProducts(ID int,ProductsNumber varchar(50))
insert MyProducts select 1 ,'S98001,S95463,S27356'
insert MyProducts select 2 ,'T19883,S56765,W34548'
select top 1000 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
select
a.ID,vc=substring(a.ProductsNumber,b.ID,charindex(',',a.ProductsNumber+',',b.ID)-b.ID)
from
MyProducts a,#Num b
where
charindex(',',','+a.ProductsNumber,b.ID)=b.ID
--示例数据
CREATE TABLE MyProducts(ID int,ProductsNumber varchar(50))
insert MyProducts select 1 ,'S98001,S95463,S27356'
insert MyProducts select 2 ,'T19883,S56765,W34548'
--1. 记录数统计示例
--分拆处理需要的辅助表(由于是直接处理,所以根据col1列中最大的数据长度来创建)
DECLARE @len int
SELECT TOP 1 @len=LEN(col)+1 FROM tb ORDER BY LEN(col) DESC
IF ISNULL(@len,1)=1 RETURN
SET ROWCOUNT @len
SELECT ID=IDENTITY(int,1,1) INTO # FROM syscolumns a,syscolumns b
ALTER TABLE # ADD PRIMARY KEY(ID)
SET ROWCOUNT 0
--统计处理
SELECT data=SUBSTRING(a.ProductsNumber,b.ID,CHARINDEX(',',a.ProductsNumber+',',b.ID)-b.ID)
--[COUNT]=COUNT(DISTINCT a.ID),
--Numbers=COUNT(*)
FROM MyProducts a,# b
WHERE b.ID<=LEN(a.ProductsNumber)
AND SUBSTRING(','+a.ProductsNumber,b.ID,1)=','
GROUP BY SUBSTRING(a.ProductsNumber,b.ID,CHARINDEX(',',a.ProductsNumber+',',b.ID)-b.ID)
DROP TABLE #
GO
--示例数据
CREATE TABLE tb(ID int,col varchar(50))
insert tb select 1 ,'S98001,S95463,S27356'
insert tb select 2 ,'T19883,S56765,W34548'
--1. 记录数统计示例
--分拆处理需要的辅助表(由于是直接处理,所以根据col1列中最大的数据长度来创建)
DECLARE @len int
SELECT TOP 1 @len=LEN(col)+1 FROM tb ORDER BY LEN(col) DESC
IF ISNULL(@len,1)=1 RETURN
SET ROWCOUNT @len
SELECT ID=IDENTITY(int,1,1) INTO # FROM syscolumns a,syscolumns b
ALTER TABLE # ADD PRIMARY KEY(ID)
SET ROWCOUNT 0
--统计处理
SELECT data=SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
--[COUNT]=COUNT(DISTINCT a.ID),
--Numbers=COUNT(*)
FROM tb a,# b
WHERE b.ID<=LEN(a.col)
AND SUBSTRING(','+a.col,b.ID,1)=','
GROUP BY SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
DROP TABLE #
GO
if object_id('Tempdb..#Num') is not null
drop table #Num
go
select top 1000 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
go
select
a.id,vc=substring(a.vc,b.ID,charindex(',',a.vc+',',b.ID)-b.ID)
from
#t a,#Num b
where
charindex(',',','+a.vc,b.ID)=b.ID
/**
id vc
----------- ---------------------------
1 S98001
1 S95463
1 S27356
2 T19883
2 S56765
2 W34548
(6 行受影响)
**/
create table #t(id int,vc varchar(200))
insert #t select 1 ,'S98001,S95463,S27356'
insert #t select 2 ,'T19883,S56765,W34548'
select row_number() over(order by (select 1)) as id
,c.value('.','varchar(50)') as list
from #t a
cross apply(select cast('<row>'+replace(vc,',','</row><row>')+'</row>' as xml) as xmlcode) C1
cross apply xmlcode.nodes('*') t(c)
id list
-------------------- --------------------------------------------------
1 S98001
2 S95463
3 S27356
4 T19883
5 S56765
6 W34548
(6 行受影响)
create table #t(id int,vc varchar(200))
insert #t select 1 ,'S98001,S95463,S27356'
insert #t select 2 ,'T19883,S56765,W34548'
select c.value('.','varchar(50)') as list from #t a
cross apply(select cast('<row>'+replace(vc,',','</row><row>')+'</row>' as xml) as xmlcode) C1
cross apply xmlcode.nodes('*') t(c)
list
--------------------------------------------------
S98001
S95463
S27356
T19883
S56765
W34548
(6 行受影响)