使用STUFF连接以逗号分隔的字符串

Question

我想连接逗号分隔的字符串。我有一个查询，我正在使用该函数来获取工作人员。

目前，我使用[dbo].fn_fullname(A.REFNO)作为Staff来获取Staff，但我想包含一些条件，如果cast(S.SCH_NO as varchar) as ReferenceIdentifier的计数大于1，则连接该值。但不确定如何在视图内部实现。

这里我试图使用STUFF来连接。

下面是查询:

Alter View vw_TestDB
AS
select 
NEWID() as UniqueKey,
P.ID as Number, 
cast(S.SCH_NO as varchar) as ReferenceIdentifier, 
cast(S.START_D as datetime2) as StartDateTime,                                      
staff = STUFF((
SELECT ',' + [dbo].fn_fullname(A.REFNO)
FROM [dbo].[vw_TESTDB]
FOR XML PATH('')
), 1, 1, '')
FROM [dbo].[vw_TestDB]
group by ReferenceIdentifier
having count(ReferenceIdentifier)>1,

[dbo].fn_fullname(A.REFNO) as Staff,

from [dbo].V_SCHEDULES S WITH (NOLOCK)
inner join [dbo].V_PAT P WITH (NOLOCK) on P.PAT_REFNO = S.PAT_REFNO

函数:

ALTER FUNCTION [dbo].[fn_fullname]
(
@refno as numeric(10, 0)
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @name     as varchar(100)
SELECT  @name = Stuff(Coalesce(' ' + 
CASE 
WHEN proca.TITLE_REFNO = 3104 
THEN NULL
ELSE
NullIf(dbo.fn_rfval(proca.TITLE_REFNO), '')
END,
'') +
Coalesce(' ' + proca.forename, '') +
Coalesce(' ' + proca.surname, ''),
1, 
1,
'') 
FROM    dbo.v_carers_active proca (nolock)
WHERE   refno = @refno
return @name
END
GO

以下是样本数据

D Kirkpatrick女士Deb Kirkpatrick女士

UniqueKey	Number	ReferenceIdentifier	StartDateTime	staff
70DB83D1-2900-4CF1-9CC4-CA6948AC0E91	A4286	2182823	2015-03-26 08:00:00.0000000	Ms S Taylor
310745CB-4724-4724-A5F0-7D9088317E58	A4286	2182823	2015-03-26 08:00:00.0000000
CA6DDB25-AADD-4FC1-ABAA-2AF84016E6E5	A4286	2182834	2015-03-19 08:00:00.0000000	D Kirkpatrick女士
6A3C0A3B-EAA3-4523-B4FD-2882E2C02B4A	A4286	2182844	2015-03-30 08:00:00.0000000	Mrs Nel McKinnon
6399662A-EC4D-4993-8D4F-0BC396D12C2C	A4286	2182844	2015-03-30 08:00:00.0000000

不太明白你想要什么。根据样例数据和预期输出，您似乎想要

SELECT  t.[Number], t.[ReferenceIdentifier], t.[StartDatetime],
STUFF (
(SELECT ',' + [dbo].fn_fullname(x.REFNO)
FROM   yourtable AS x
WHERE  x.[Number] = t.[Number]
AND    x.[ReferenceIdentifier] = t.[ReferenceIdentifier]
AND    x.[StartDatetime] = t.[StartDatetime]
FOR XML PATH('')),
1, 1, '') AS Staff
FROM    yourtable t
GROUP BY t.[Number], t.[ReferenceIdentifier], t.[StartDatetime]
HAVING COUNT(*) > 1