----------------------列拆分和合并---------------------
declare @tb table (id int,value varchar(30))
insert into @tb select 1,'a,bb'
union all select 2,'a,bb,ccc,dddd'
---方法1:
--select id,value=substring(value,number,charindex(',',value+',',number)-number) from @tb a ,master..spt_values b
-- where substring(','+value,number,1)=',' and type='p'
---方法2:
select a.id,b.va from
(select id,value=convert(xml,'<root><v>'+replace(value,',','</v><v>')+'</v></root>') from @tb) a
outer apply
(select va= n.v.value('(.)','varchar(100)') from A.value.nodes('root/v') n(v)) b
go
----合并方法1
if object_id('tb') is not null
drop table tb
if object_id('c_f') is not null
drop function c_f
go
create table tb (id int,value varchar(30))
insert into tb select 1,'a'
union all select 1,'bb'
union all select 2,'a'
union all select 2,'bb'
union all select 2,'ccc'
union all select 2,'dddd'
go
create function c_f(@id int)
returns nvarchar(100)
as
begin
declare @value nvarchar(100)
set @value='';
select @value=@value+','+ value from tb where id=@id
set @value=stuff(@value,1,1,'')
return @value
end
go
select id,dbo.c_f(id) from tb group by id
---方法2
select id ,value=stuff((select ','+value from tb where a.id=id for xml path('')),1,1,'')
from tb a group by id
---方法3
select * from
(select distinct id from tb) a
outer apply
(select d=stuff(replace(replace((select value from tb where a.id=id for xml auto),
'<tb value="',','),'"/>',''),1,1,'')) b
----------结果
(2 行受影响)
id va
----------- ----------------------------------------------------------------------------------------------------
1 a
1 bb
2 a
2 bb
2 ccc
2 dddd
(6 行受影响)
(6 行受影响)
id
----------- ----------------------------------------------------------------------------------------------------
1 a,bb
2 a,bb,ccc,dddd
(2 行受影响)
id value
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 a,bb
2 a,bb,ccc,dddd
(2 行受影响)
id d
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 a,bb
2 a,bb,ccc,dddd
(2 行受影响)