---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-07 10:11:07
---------------------------------
--> 生成测试数据表-tbif not object_id('tb') is null
drop table tb
Go
Create table tb([Y_id] int,[Y_value] nvarchar(20))
Insert tb
select 1,'1,2,3,4,5,6' union all
select 2,'2,3,4,5,6,7,8,9'
Go
--Select * from tb-->SQL查询如下:
;with t as
(
select
a.Y_id,
Y_value=case when row_number() over(partition by Y_id order by getdate())%2=0
then cast(cast(substring(a.Y_value, b.number, charindex(',', a.Y_value + ',', b.number) - b.number) as int)*2 as nvarchar(20))
else substring(a.Y_value, b.number, charindex(',', a.Y_value + ',', b.number) - b.number)
end
from tb a,master..spt_values b
where b.type='p'
and substring(',' + a.Y_value,b.number,1) = ','
)
select distinct Y_id,Y_value=stuff((select ','+Y_value from t a where a.Y_id=t.Y_id for xml path('')),1,1,'') from t/*
Y_id Y_value
----------- -------------------------
1 1,4,3,8,5,12
2 2,6,4,10,6,14,8,18(2 行受影响)
*/
--> 我的淘宝: http://shop36766744.taobao.com/if object_id('[tab]') is not null drop table [tab]
create table [tab]([Y_id] int,[Y_value] varchar(15))
insert [tab]
select 1,'1,2,3,4,5,6' union all
select 2,'2,3,4,5,6,7,8,9'
SELECT TOP 8000 id = identity(int,1,1) INTO tmp FROM syscolumns a, syscolumns b select distinct c.Y_id , c.Y_value into tt from
(
SELECT A.Y_id,Y_value = SUBSTRING(A.Y_value, B.ID, CHARINDEX(',', A.Y_value + ',', B.ID) - B.ID) FROM tab A, tmp B WHERE SUBSTRING(',' + a.Y_value, B.id, 1) = ','
) c
order by Y_idcreate function dbo.f_str(@id int) returns varchar(100)
as
begin
declare @str varchar(1000)
select @str = isnull(@str + ',','') + cast((case when Y_value%2=0 then Y_value*2 else Y_value end) as varchar) from tt where Y_id = @id
return @str
end
goselect Y_id ,dbo.f_str(Y_id) from tt group by Y_id /*
Y_id
----------- ----------------------------------------------------------------------------------------------------
1 1,4,3,8,5,12
2 4,3,8,5,12,7,16,9（所影响的行数为 2 行）*/drop table tmp,tt
drop function dbo.f_str
create table Test(Y_id int, Y_value varchar(30))
insert into Test select 1,'1,2,3,4,5,6'
insert into Test select 2,'2,3,4,5,6,7,8,9'
insert into Test select 3,'2,32,41,5,6,71,8,9'
GOwith Play1
as
(
select
case when row_number() over (partition by Y_id order by B.number)%2 =0 then
convert(int,SUBSTRING(A.Y_value,B.number,CHARINDEX(',',A.Y_value+',',B.number)-B.number))*2
else convert(int,SUBSTRING(A.Y_value,B.number,CHARINDEX(',',A.Y_value+',',B.number)-B.number))
end AS [value],
Y_id
FROM Test as A
JOIN master.dbo.spt_values AS B
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.Y_value)
AND SUBSTRING(','+A.Y_value,B.number,1)=','
)select A.Y_id,B.[value]
from
(select distinct Y_id from Play1) as A
outer apply
(select stuff (replace(replace((select [value] from Play1 where Y_id=A.Y_id for XML auto),'<Play1 Value="',','),'"/>',''),1,1,'') as [Value]
) as B/*
Y_id Name
------ ------------------
1 1,4,3,8,5,12
2 2,6,4,10,6,14,8,18
3 2,64,41,10,6,142,8,18
*/
GO
Drop table Test
Insert TB
select 1,'1,2,3,4,5,6' union all
select 2,'2,3,4,5,6,7,8,9'
Go
;WITH CTE
AS
(
SELECT A.[Y_id], [Y_value]=(case when B.[Y_value]%2=0 then B.[Y_value]*2 else B.[Y_value] end)
FROM(
SELECT [Y_id], [Y_value] = CONVERT(xml,' <root> <v>'
+ REPLACE([Y_value], ',', ' </v> <v>') + ' </v> </root>') FROM TB
)A
OUTER APPLY(
SELECT [Y_value] = N.v.value('.', 'varchar(100)') FROM A.[Y_value].nodes('/root/v') N(v)
)B
)SELECT [Y_id], [Y_value]=STUFF((SELECT ','+ltrim([Y_value]) FROM CTE WHERE [Y_id]=t.[Y_id] FOR XML PATH('')), 1, 1, '')
FROM CTE t
GROUP BY [Y_id]
Y_id Y_value
----------- -----------------------
1 1,4,3,8,5,12
2 4,3,8,5,12,7,16,9(2 行受影响)
其实楼主的要求完全可以用函数来实现。--创建测试数据
if not object_id('LI') is null
drop table LI
Go
Create table LI([Y_id] int,[Y_value] nvarchar(20))
Insert LI
select 1,'1,2,3,4,5,6' union all
select 2,'2,3,4,5,6,7,8,9'
Go--创建函数
Create Function dbo.F_GetNewStr(@strInput varchar(50))
returns varchar(50)
As
begin
Declare @Rst varchar(50),@i int,@iZ int,@Mark varchar(50)
Select @strInput = @strInput + ',',@i = 0,@Rst = '',@iZ = 1,@Mark = ''
while @i < len(@strInput)
begin
if (@iZ = 1)
Begin
Select @Rst = @Rst + Left(@strInput,charindex(',',@strInput)-1)
End
else
begin
Select @Mark = substring(@strInput,@i + 1,len(@strInput)- @i)
if (@iZ % 2 = 1)
Begin
Select @Rst = @Rst + ',' + left(@Mark,charindex(',',@Mark) -1)
End
else if (@iZ % 2 = 0)
Begin
Select @Rst = @Rst + ',' + Cast(Cast(left(@Mark,charindex(',',@Mark) -1) as int) * 2 as varchar)
End
end Select @i = @i + 2
Select @iZ = @iZ + 1
end
return @Rst
endGO
--结果
Select Y_ID,dbo.F_GetNewStr(Y_Value) as Y_value
from LI
Y_id Y_value
----------- -----------------------
1 1,4,3,8,5,12
2 4,3,8,5,12,7,16,9(2 行受影响)
Y_id Y_value
1 1,4,3,8,5,12
2 2,6,4,10,6,14,8,18 贴错了。
alter function GetNewStr(@strInput varchar(100),@ char(1))
returns varchar(100)
as
begin
declare @strOut varchar(100),@pos int,@count int
set @pos=1
set @count=0
set @strInput=@strInput+@
set @strOut=''
while @pos>0
begin
set @pos=charindex(@,@StrInput)
if(@pos>0)
begin
set @count=@count+1
if @count%2=0
select @strOut=@strOut+@+Convert(varchar,Convert(int,substring(@strInput,1,@pos-1))*2)
else
select @strOut=@strout+@+substring(@strInput,1,@pos-1)
end
set @StrInput=substring(@StrInput,@pos+1,len(@strInput))
end
return substring(@strOut,2,len(@strOut))
end
这个函数我测试成功