一个同样情况的例子,请参考:有table1:
|comp_code|staff|
|1 |gg |
|1 |jj |
|2 |dd |
|2 |mm |如何可以得出这样的结果
|comp_code|staff|
|1 |gg,jj|
|2 |dd,mm|
create table #table1(
comp_code int,
staff char(2))insert #table1 select 1, 'gg'
insert #table1 select 1, 'jj'
insert #table1 select 2, 'dd'
insert #table1 select 2, 'mm'create table #t(
comp_code int,
staff varchar(100))declare @s varchar(100)
declare @i int
select @i = min(comp_code) -1 from #table1
while exists (select * from #table1 where comp_code > @i)
begin
select @i = min(comp_code) from #table1 where comp_code > @i
set @s = ''
select @s = @s + staff + ',' from #table1 where comp_code = @i
insert #t select @i, left(@s, len(@s) -1)
end
select * from #t order by comp_code
|comp_code|staff|
|1 |gg |
|1 |jj |
|2 |dd |
|2 |mm |如何可以得出这样的结果
|comp_code|staff|
|1 |gg,jj|
|2 |dd,mm|
create table #table1(
comp_code int,
staff char(2))insert #table1 select 1, 'gg'
insert #table1 select 1, 'jj'
insert #table1 select 2, 'dd'
insert #table1 select 2, 'mm'create table #t(
comp_code int,
staff varchar(100))declare @s varchar(100)
declare @i int
select @i = min(comp_code) -1 from #table1
while exists (select * from #table1 where comp_code > @i)
begin
select @i = min(comp_code) from #table1 where comp_code > @i
set @s = ''
select @s = @s + staff + ',' from #table1 where comp_code = @i
insert #t select @i, left(@s, len(@s) -1)
end
select * from #t order by comp_code
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A
create table test(a char(1),b char(1))insert test values('1','1')
insert test values('1','2')
insert test values('2','1')
insert test values('2','2')
insert test values('2','3')
insert test values('2','4')create function getstr(@content varchar(1))
returns varchar(100)
as
begin
declare @str varchar(100)
set @str=''
select @str=@str+','+b from test where a=@content
select @str=right(@str,len(@str)-1)
return @str
end
go--调用:
select a,dbo.getstr(a) b from Test group by a/*
a b
---- ---------------
1 1,2
2 1,2,3,4(所影响的行数为 2 行)
*/