create table tb(
id int,
value nvarchar(50)
)
insert into tb values(1, 'aa') ;
insert into tb values(1, 'bb');
insert into tb values(2, 'aaa') ;
insert into tb values(2, 'bbb');
insert into tb values(2, 'ccc') ;
go
select * from tb
--1. 创建处理函数
create function dbo.f_value(@id int)
returns varchar(8000)
as
begin declare @str varchar(8000)
set @str = ''
select @str = @str + '/' + value from tb
where id=@id
return stuff(@str, 1, 1, '')
end
GO
-- 调用函数
select id, value = dbo.f_value(id) from tb group by id
-- 删除表
drop table tb
-- 删除函数
drop function dbo.f_value
-----------------------------------------------
只需要把里面的SQL语句改成你想要的就行了
SQL Server:
CREATE FUNCTION dbo.f_position(@ClassID BIGINT)
RETURNS VARCHAR(8000)
AS
BEGIN DECLARE @str VARCHAR(8000)
SET @str = ''
SELECT @str = @str + (dbo.ufn_GetLocalizedCodeName([StevenhMembers].[Position], 'en-CA')) + '_' + isnull([StevenhMembers].[MemberName] ,'') + ','
FROM [ReadModel].[StevenhMembers]
WHERE [StevenhMembers].[ClassID]=@ClassID AND dbo.ufn_GetLocalizedCodeName([StevenhMembers].[Position], 'en-CA') in ('Teacher', 'Monitor')
ORDER BY Position
RETURN substring(@str,1,len(@str)-1) --去除最后一个逗号
END
go
select ClassID,MemberName = dbo.f_position(ClassID) from [Model].[StevenhMembers] WHERE dbo.ufn_GetLocalizedCodeName([StevenhMembers].[Position], 'en-CA') in ('Teacher', 'Monitor') GROUP BY ClassID
StevenhMembers表: