--创建一个处理函数 create function f_str( @id int )returns varchar(8000) as begin declare @r varchar(8000) set @r='[' select @r=@r+item from A where id=@id return(@r+']') end go--调用函数实现查询 select id,item=dbo.f_str(id) from a group by id
参考 --生成 F1 F2 F1 F2 -------------------- ------------------------------ a c a c,d,e a d =======> b f,g,h a e b f b g b h--示例数据 create table tb(F1 varchar(10),F2 varchar(10)) insert tb select 'a','c' union all select 'a','d' union all select 'a','e' union all select 'b','f' union all select 'b','g' union all select 'b','h' go--用一条sql语句实现(实际是没有一条sql语句实现的,这里是取巧) exec('set nocount on select f1,f2=cast(f2 as varchar(8000)) into #t from tb order by f1 declare @f1 varchar(10),@r varchar(8000) update #t set @r=case f1 when @f1 then @r+'',''+f2 else f2 end,f2=@r,@f1=f1 select F1,F2=max(f2) from #t group by f1 ') go
create function f_str(
@id int
)returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r='['
select @r=@r+item from A where id=@id
return(@r+']')
end
go--调用函数实现查询
select id,item=dbo.f_str(id) from a group by id
--生成
F1 F2 F1 F2
-------------------- ------------------------------
a c a c,d,e
a d =======> b f,g,h
a e
b f
b g
b h--示例数据
create table tb(F1 varchar(10),F2 varchar(10))
insert tb select 'a','c'
union all select 'a','d'
union all select 'a','e'
union all select 'b','f'
union all select 'b','g'
union all select 'b','h'
go--用一条sql语句实现(实际是没有一条sql语句实现的,这里是取巧)
exec('set nocount on
select f1,f2=cast(f2 as varchar(8000)) into #t from tb order by f1
declare @f1 varchar(10),@r varchar(8000)
update #t set @r=case f1 when @f1 then @r+'',''+f2 else f2 end,f2=@r,@f1=f1
select F1,F2=max(f2) from #t group by f1
')
go