create table #a(id varchar(50),cert varchar(50))
insert into #a (0, 'abc ')
insert into #a (0, 'def ')
insert into #a (0, 'gkh ')
insert into #a (1, 'abc ')
insert into #a (1, 'mn ')
insert into #a (2, 'abc ')
i want to get the result like follow:
result:
0,( 'abc ', 'def ',gkh)
1,( 'abc ', 'mn ')
2,( 'abc ')
help me. thanks
------解决方案--------------------
create function fun_test(@cid varchar(2000))
returns varchar(2000)
as
begin
declare @chr varchar(2000)
set @chr= '( '
select @[email protected]+c+ ', ' from #a where [email protected]
set @[email protected]+ ') '
return @chr
end
select *,dbo.fun_test(id)from #a
------解决方案--------------------
create table #a(id varchar(50),cert varchar(50))
insert into #a values( '0 ', 'abc ')
insert into #a values( '0 ', 'def ')
insert into #a values( '0 ', 'gkh ')
insert into #a values( '1 ', 'abc ')
insert into #a values( '1 ', 'mn ')
insert into #a values( '2 ', 'abc ')
select * into t from #a
create function dbo.aaa(@id varchar(1000))
returns varchar(100)
as
begin
declare @s varchar(100)
set @s= ' '
select @[email protected] + ', '+cert from t where [email protected]
return(stuff(@s,1,1, ' '))
end
select id, dbo.aaa(id) as name from t group by id
id name
-------------------------------------------------- ----------------------------------------------------------------
0 abc,def,gkh
1 abc,mn
2 abc
(3 row(s) affected)