从SQL一个表的字段中取出被逗号分割的数据.

[modernxxx]

各位SQL高手,需要大家帮忙了. 有一个表,表中的数据是用逗号分隔的,如下所示

表: MyProducts

字段: ProductNumber (varchar类型的)

数据如下所示

1. S98001,S95463,S27356

2. T19883,S56765,W34548

......................(继续,有几百条)

现在我想用一个视图将这些被逗号分隔的数据取出来,取出来的效果应该是下面这样显示的.

1.S98001
2.S95463
3.S27356
4.T19883
5.S56765
6.W34548
........


大家有什么好办法吗?

谢谢!

[东那个升]

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 行受影响)

[东那个升]

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 行受影响)

[modernxxx]

谢谢回复,我目前用的是SQL2000, 你的代码似乎不行哦.

[百年树人]

[Quote=引用 3 楼 modernxxx 的回复:]
谢谢回复,我目前用的是SQL2000, 你的代码似乎不行哦.
[/Quote]
sql2000借助一个临时表，借1楼的数据

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 行受影响)
**/

更多分拆表的方法请参考精华帖 合并分拆表_整理贴1

[xiaoxiao8372]

东升哥的，看不懂，c.value('.','varchar(50)')这是什么意思啊。那儿有c这个东东。t(c)这个又是什么啊。！

[modernxxx]

[Quote=引用 4 楼 josy 的回复:]
引用 3 楼 modernxxx 的回复:
谢谢回复,我目前用的是SQL2000, 你的代码似乎不行哦.

sql2000借助一个临时表，借1楼的数据

SQL code
if object_id('Tempdb..#Num') is not null
drop table #Num
go
select top 1000 ID=Identity(int,1,1) into……
[/Quote]


josy大哥,我SQL不是高手哦,能不能请你帮忙写的详细点哦.

表名是MyProducts 字段名是ProductsNumber

应该怎么建立视图哦

[duanzhi1984]

看看这个是否满足要求

--示例数据  
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
 

[duanzhi1984]

--示例数据  
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

用视图就麻烦点了。最好用过程

[modernxxx]

[Quote=引用 7 楼 duanzhi1984 的回复:]
看看这个是否满足要求


SQL code
--示例数据
CREATE TABLE tb(ID int,col varchar(50))
insert tb select 1 ,'S98001,S95463,S27356'
insert tb select 2 ,'T19883,S56765,W34548'


--1. 记录数统计示例
--分拆处理需要的辅助表(由于是直……
[/Quote]

杜安哥哥,方法是对的,但我就是不知道如何将dbo.MyProducts.ProductNumber 放到你这里面实现呢?

[duanzhi1984]

[Quote=引用 9 楼 modernxxx 的回复:]
引用 7 楼 duanzhi1984 的回复:
看看这个是否满足要求


SQL code
--示例数据
CREATE TABLE tb(ID int,col varchar(50))
insert tb select 1 ,'S98001,S95463,S27356'
insert tb select 2 ,'T19883,S56765,W34548'


--1. 记录数……
[/Quote]

8楼已回复

[duanzhi1984]

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

[modernxxx]

8楼的还是不多哦. 没有读取Myproducts表中PRODUCTSNUMBER字段的数据,只是建立了一个临时表,而且用的数据也是我一开始发上来的演示数据.

[modernxxx]

没人知道了吗?

[xiaoxiao8372]

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)

[sych888]

create function splitstr2()
returns @t table(id int identity,col varchar(10))
as
begin
declare @ss varchar(100)
declare @var varchar(1)
set @var=','
declare content_cursor cursor for select vc from testTableFunc
open content_cursor
while(@@fetch_status=0)
begin
fetch next from content_cursor into @ss
while(charindex(@var,@ss)>0)
begin
insert into @t values(substring(@ss,1,charindex(@var,@ss)-1))
set @ss=substring(@ss,charindex(@var,@ss)+1,len(@ss)-charindex(@var,@ss)+1)
end
insert into @t values(@ss)
end
close content_cursor
deallocate content_cursor
return
end

select * from dbo.splitstr2()

[sych888]

[Quote=引用 15 楼 sych888 的回复:]
create function splitstr2()
returns @t table(id int identity,col varchar(10))
as
begin
declare @ss varchar(100)
declare @var varchar(1)
set @var=','
declare content_cursor cursor for selec……
[/Quote]

create table testTableFunc(id int,vc varchar(200))
insert testTableFunc select 1 ,'S98001,S95463,S27356'
insert testTableFunc select 2 ,'T19883,S56765,W34548'