CREATE FUNCTION [dbo].[FN_GetDepStaffNamesByDepID]
(
@classid nvarchar(50)
)
RETURNS [nvarchar](4000)
AS
BEGIN
DECLARE @ReturnValue [nvarchar](4000)
SET @ReturnValue = ''
SELECT @ReturnValue=@ReturnValue + LTRIM(RTRIM(Class.student)) + ' '
FROM class
WHERE Class.classid = @classid
SET @ReturnValue = ISNULL(@ReturnValue,';')
RETURN @ReturnValue
END
------查询语句-------
SELECT classid, [dbo].[FN_GetDepStaffNamesByDepID](classid) As student
FROM class
GROUP BY classid
classid | student |
一班 | 张三 李四 王老五 |
二班 | 刘六 孙七 |
三班 | 李八 |