table temp
a b c d
1 1 0 456
1 1 0 789
1 1 1 456
1 1 1 789
1 2 0 456
1 2 0 789
1 2 1 456
1 2 1 789
1 3 0 456
1 3 0 789
1 3 1 456
1 3 1 789 想得到
a b c d
1 1,2,3 0,1 456
1 1,2,3 0,1 789
a b c d
1 1 0 456
1 1 0 789
1 1 1 456
1 1 1 789
1 2 0 456
1 2 0 789
1 2 1 456
1 2 1 789
1 3 0 456
1 3 0 789
1 3 1 456
1 3 1 789 想得到
a b c d
1 1,2,3 0,1 456
1 1,2,3 0,1 789
create table tb(a int,b int,c int,d int)
insert into tb
select 1 , 1 , 0 , 456 union
select 1 , 1 , 0 , 789 union
select 1 , 1 , 1 ,456 union
select 1 , 1 , 1 , 789 union
select 1 , 2 , 0 , 456 union
select 1 , 2 , 0 , 789 union
select 1 , 2 , 1 , 456 union
select 1 , 2 , 1 , 789 union
select 1 , 3 , 0 , 456 union
select 1 , 3 , 0 , 789 union
select 1 , 3 , 1 , 456 union
select 1 , 3 , 1 , 789
go--辅助函数
create function fns_getStr11(@a int,@flag bit)
returns varchar(1000)
as
begin
declare @str varchar(1000)
select @str=''
if @flag=1
select @str=@str+','+convert(varchar,b) from tb where a=@a group by b
else
select @str=@str+','+convert(varchar,c) from tb where a=@a group by c
select @str=stuff(@str,1,1,'')
return @str
end
go
--查询
select a,b=dbo.fns_getstr11(a,1),c=dbo.fns_getstr11(a,0),d
from tb
group by a,d--删除测试表及函数
drop table tb
drop function fns_getstr11
go
/*结果
1 1,2,3 0,1 456
1 1,2,3 0,1 789*/
insert temp
select 1,1,0,456 union
select 1,1,0,789 union
select 1,1,1,456 union
select 1,1,1,789 union
select 1,2,0,456 union
select 1,2,0,789 union
select 1,2,1,456 union
select 1,2,1,789 union
select 1,3,0,456 union
select 1,3,0,789 union
select 1,3,1,456 union
select 1,3,1,789 create function f_GetBStr(@a int,@d int)
returns varchar(2000)
as
begin
declare @s varchar(2000)
set @s=''
select @s=@s+convert(varchar,b)+','
from (select distinct b from temp where a=@a and d=@d ) t1
select @s=left(@s,len(@s)-1)
return(@s)
endcreate function f_GetCStr(@a int,@d int)
returns varchar(2000)
as
begin
declare @s varchar(2000)
set @s=''
select @s=@s+convert(varchar,c)+','
from (select distinct c from temp where a=@a and d=@d ) t1
select @s=left(@s,len(@s)-1)
return(@s)
endselect a,b=dbo.f_GetBStr(a,d),c=dbo.f_GetCStr(a,d),d
from temp
group by a,d