if OBJECT_ID('tab') is not null drop table tab go create table tab(A varchar(2),B varchar(10)) insert into tab select 'a', '1,2' union all select 'a', '2,3' union all select 'a', '4' union all select 'b', '1'
select A,B=STUFF((select ','+B from (select DISTINCT A ,B=SUBSTRING(a.B,number,CHARINDEX(',',a.B+',',number)-b.number) from tab a join master..spt_values b on b.type='P' and CHARINDEX(',',','+a.B,number)=number ) c where A=a.A for xml path('')),1,1,'') from (select DISTINCT A ,B=SUBSTRING(a.B,number,CHARINDEX(',',a.B+',',number)-b.number) from tab a join master..spt_values b on b.type='P' and CHARINDEX(',',','+a.B,number)=number) a group by AA B a 1,2,3,4 b 1
--> 测试数据: [tab] if object_id('[tab]') is not null drop table [tab] create table [tab] (A varchar(50),B varchar(50)) insert into [tab] select 'a','1,2' union all select 'a','2,3' union all select 'a','4' union all select 'b','1' goalter function get_str(@a varchar(50)) returns varchar(1000) as begin declare @str varchar(1000) set @str='' select @str=@str+b+',' from tab where a=@a declare @temp varchar(1000) set @temp='' while(charindex(',',@str)>0) begin if(charindex(','+substring(@str,1,charindex(',',@str)-1)+',',','+@temp+',')=0) set @temp=@temp+substring(@str,1,charindex(',',@str)-1)+',' set @str=stuff(@str,1,charindex(',',@str),'') end if(@temp!='') set @temp=left(@temp,len(@temp)-1) return @temp end go select A,b=dbo.get_str(a) from [tab] group by a--结果: A b -------------------------------------------------- ----------- a 1,2,3,4 b 1
if OBJECT_ID('tab') is not null
drop table tab
go
create table tab(A varchar(2),B varchar(10))
insert into tab
select 'a', '1,2' union all
select 'a', '2,3' union all
select 'a', '4' union all
select 'b', '1'
select A,B=STUFF((select ','+B
from (select DISTINCT A ,B=SUBSTRING(a.B,number,CHARINDEX(',',a.B+',',number)-b.number)
from tab a join master..spt_values b on b.type='P'
and CHARINDEX(',',','+a.B,number)=number ) c
where A=a.A for xml path('')),1,1,'')
from (select DISTINCT A ,B=SUBSTRING(a.B,number,CHARINDEX(',',a.B+',',number)-b.number)
from tab a join master..spt_values b on b.type='P'
and CHARINDEX(',',','+a.B,number)=number) a
group by AA B
a 1,2,3,4
b 1
出现以下错误:
服务器: 消息 170,级别 15,状态 1,行 5
第 5 行: 'xml' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 8
第 8 行: 'a' 附近有语法错误。
if object_id('[tab]') is not null drop table [tab]
create table [tab] (A varchar(50),B varchar(50))
insert into [tab]
select 'a','1,2' union all
select 'a','2,3' union all
select 'a','4' union all
select 'b','1'
goalter function get_str(@a varchar(50))
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+b+',' from tab where a=@a
declare @temp varchar(1000)
set @temp=''
while(charindex(',',@str)>0)
begin
if(charindex(','+substring(@str,1,charindex(',',@str)-1)+',',','+@temp+',')=0)
set @temp=@temp+substring(@str,1,charindex(',',@str)-1)+','
set @str=stuff(@str,1,charindex(',',@str),'')
end
if(@temp!='')
set @temp=left(@temp,len(@temp)-1)
return @temp
end
go
select A,b=dbo.get_str(a) from [tab] group by a--结果:
A b
-------------------------------------------------- -----------
a 1,2,3,4
b 1