一条记录变多条

[OCRS]

原纪录：
11 AA,BB,CC,DD
想变成
11 AA
11 BB
11 CC
11 DD
怎么写语句？

[wzy_love_sly]

declare @t table (id int,name varchar(40))
insert into @t select 1,'AA,BB,CC,DD'

select id,parseName(replace(name,',','.'),4) from @t union all
select id,parseName(replace(name,',','.'),3) from @t union all
select id,parseName(replace(name,',','.'),2) from @t union all
select id,parseName(replace(name,',','.'),1) from @t

1 AA
1 BB
1 CC
1 DD

[OCRS]

不能这样吧，我举的是样本数据，还有很多呢

[liangCK]

create table tb(id int,name varchar(500))
insert tb values(11,'AA,BB,CC,DD')

select top 500 id=identity(int,1,1) into # from syscolumns a,syscolumns b

select *
from
(
    select a.id,name=substring(a.name,b.id,charindex(',',a.name+',',b.id)-b.id)
    from tb a,# b
    where b.id<=len(a.name) and substring(','+a.name,b.id,1)=','
) x

drop table tb,#

/*
id          name         
----------- ----------
11          AA
11          BB
11          CC
11          DD

（所影响的行数为 4 行）

*/

[liangCK]

create table tb(id int,name varchar(500))
insert tb values(11,'AA,BB,CC,DD')
insert tb values(22,'XX,YY,ZZ,WW')

select top 500 id=identity(int,1,1) into # from syscolumns a,syscolumns b

select *
from
(
    select a.id,name=substring(a.name,b.id,charindex(',',a.name+',',b.id)-b.id)
    from tb a,# b
    where b.id<=len(a.name) and substring(','+a.name,b.id,1)=','
) x

drop table tb,#

/*
id          name    
----------- ----------
11          AA
11          BB
11          CC
11          DD
22          XX
22          YY
22          ZZ
22          WW

（所影响的行数为 8 行）
*/

[OCRS]

谢谢了，但其实我是想使用视图的方式，将一个表的数据展示出来，能不能不用额外的TABLE呢？

[liangCK]

如果该列长度不是很长..则可以

create table tb(id int,name varchar(500))
insert tb values(11,'AA,BB,CC,DD')
insert tb values(22,'XX,YY,ZZ,WW')

select id,name
from
(
    select a.id,name=substring(a.name,b.id,charindex(',',a.name+',',b.id)-b.id)
    from tb a,(select id=1 union all select 2 union all select 3 union all select 4 union all select 5
               union all select 6 union all select 7 union all select 8 union all select 9
               union all select 10 union all select 11 union all select 12) b
    where b.id<=len(a.name) and substring(','+a.name,b.id,1)=','
) x

drop table tb

如果很长..在SQLServer2000里不用一个临时表.估计是不行.

[中国风]

有表变量

[中国风]

create table T(id int,name varchar(500))
insert T values(11,'AA,BB,CC,DD')
insert T values(22,'XX,YYY,ZZZ,WWWWW')

go
create function F_test()
returns @T table(ID int,Name nvarchar(500))
as
begin
	declare @T2 table(ID int identity,Col int)
	insert @T2 select top 501 Col=1 from syscolumns a ,syscolumns b
	
	insert @T
	select
		a.ID,
		substring(a.Name,b.ID,charindex(',',a.Name+',',b.ID)-b.ID)
	from 
		T a
	join
		@T2 b on substring(','+a.Name,b.ID,1)=','
	return
end


go

select
	*
from 
	F_test()


/*
11	AA
22	XX
11	BB
22	YYY
11	CC
22	ZZZ
11	DD
22	WWWWW

*/

[中国风]

用一个函就行了。。。与视图用法一样。。也可把函数生成一个函数..

create table T(id int,name varchar(500))
insert T values(11,'AA,BB,CC,DD')
insert T values(22,'XX,YYY,ZZZ,WWWWW')
insert T values(33,'XX,YYY,ZZZ,WWWWW,YYYYY')
go
create function F_test()
returns @T table(ID int,Name nvarchar(500))
as
begin
	declare @T2 table(ID int identity,Col int)
	insert @T2 select top 501 Col=1 from syscolumns a ,syscolumns b
	
	insert @T
	select
		a.ID,
		substring(a.Name,b.ID,charindex(',',a.Name+',',b.ID)-b.ID)
	from 
		T a
	join
		@T2 b on substring(','+a.Name,b.ID,1)=','
	order by a.ID--加个排序
	return
end


go

select
	*
from 
	F_test()

/*
11	AA
11	BB
11	CC
11	DD
22	XX
22	YYY
22	ZZZ
22	WWWWW
33	XX
33	YYY
33	ZZZ
33	WWWWW
33	YYYYY

*/

2005实现：
DECLARE @t TABLE(id int, [values] varchar(100))

INSERT @t SELECT 1, 'aa,bb'

UNION ALL SELECT 2, 'aaa,bbb,ccc'


-- 查询处理

SELECT

A.id, B.value

FROM(

SELECT id, [values] = CONVERT(xml,

'<root><v>' + REPLACE([values], ',', '</v><v>') + '</v></root>')

FROM @t

)A

OUTER APPLY(

SELECT value = N.v.value('.', 'varchar(100)')

FROM A.[values].nodes('/root/v') N(v)

)B

[JL99000]

if object_id('original') is not null
drop table original
if object_id('f_test11') is not null
drop function f_test11
go
create table original(id int,sss varchar(100))
insert original
select 11,'AA,BB,CC,DD' union all
select 22,'CC,DD,FF,EE'
SELECT * FROM original
go
create function f_test11(@id int)
returns @t table (id int,sss varchar(100))
as
begin
declare @nows varchar(100),@sub varchar(100)

select @nows = sss from original where id=@id
while (charindex(@nows,',')>0)
begin
set @sub=substring(@nows,1,charindex(@nows,',')-1)
insert into @t values(@id,@sub)
set @nows=substring(@nows,charindex(@nows,',')+1,len(@nows))
end
return
end

go

select * from dbo.f_test11(11)