--建表
create table tb (A varchar(10),B varchar(1000))
Insert into tb
select '111','AA0000,AA0001,AA0002,'
union all select '111','BB0000,BB0001,BB0002,'
union all select '222','AA0120,AA0102,AA1453,'
union all select '222','BB1212,BB3423,BB2123,'select * from tb--函數
create function dbo.fn_b(@a varchar(10))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[B] from tb where A=@a
return (@s)
end--刪除
drop table tb
drop function dbo.fn_b--結果
select A,B=dbo.fn_b(A) from tb group by A
A B
-------------------------------------------
111 AA0000,AA0001,AA0002,BB0000,BB0001,BB0002,
222 AA0120,AA0102,AA1453,BB1212,BB3423,BB2123,
create table tb (A varchar(10),B varchar(1000))
Insert into tb
select '111','AA0000,AA0001,AA0002,'
union all select '111','BB0000,BB0001,BB0002,'
union all select '222','AA0120,AA0102,AA1453,'
union all select '222','BB1212,BB3423,BB2123,'select * from tb--函數
create function dbo.fn_b(@a varchar(10))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[B] from tb where A=@a
return (@s)
end--刪除
drop table tb
drop function dbo.fn_b--結果
select A,B=dbo.fn_b(A) from tb group by A
A B
-------------------------------------------
111 AA0000,AA0001,AA0002,BB0000,BB0001,BB0002,
222 AA0120,AA0102,AA1453,BB1212,BB3423,BB2123,
create function c_str(@A)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s+','+B from 表 where A=@A
return stuff(@s,1,1,'')
end
--查询
select A,dbo.c_str(@A) from 表 group by A
Insert into tb
select '1','aaa'
union all select '1','bbb'
union all select '2','ccc'
union all select '2','ddd'select * from tb--函數
create function dbo.fn_bb(@a int)
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[B] from tb where A=@a
return (@s)
end--刪除
drop table tb
drop function dbo.fn_bb--結果
select A,B=dbo.fn_bb(A) from tb group by AA B
-----------------
1 aaabbb
2 cccddd
union
select 1 ,'bbb'
union
select 2 ,'ccc'
union
select 2 ,'ddd'
drop table bb建一函数
create function f_try(@A varchar(10))
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+rtrim(B) from bb where A=@A
return(@r)
endselect distinct A,B=dbo.f_try(A) from bb结果