Concatenate comma separated strings using STUFF

Question

I want to concatenate comma-separated string. I have a query where I am using the function to get the staff.

Currently, I am using [dbo].fn_fullname(A.REFNO) as Staff to get the staff but I want to include some conditions if the count of cast(S.SCH_NO as varchar) as ReferenceIdentifier is greater than 1 then concatenate the value. But not sure how it can be achieved inside the view itself.

Here I am trying to use STUFF to concatenate.

Here is the query:

    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

Here the function:

    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

Here are sample data

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	Ms D Kirkpatrick
CA6DDB25-AADD-4FC1-ABAA-2AF84016E6E5	A4286	2182834	2015-03-19 08:00:00.0000000	Ms 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	Ms Deb Kirkpatrick

Expected output

UniqueKey	Number	ReferenceIdentifier	StartDateTime	staff
70DB83D1-2900-4CF1-9CC4-CA6948AC0E91	A4286	2182823	2015-03-26 08:00:00.0000000	Ms S Taylor,Ms D Kirkpatrick
CA6DDB25-AADD-4FC1-ABAA-2AF84016E6E5	A4286	2182834	2015-03-19 08:00:00.0000000	Ms D Kirkpatrick
6A3C0A3B-EAA3-4523-B4FD-2882E2C02B4A	A4286	2182844	2015-03-30 08:00:00.0000000	Mrs Nel McKinnon,Ms Deb Kirkpatrick

Answer 1

Not quite understand what you wanted. Based on the sample data and expected output, it seems like you wanted

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