Here is a version which includes ordering and your requirement for a count:

DROP TABLE IF EXISTS #Test;

CREATE TABLE #Test
(
    person_id INT NOT NULL
 ...

If this is a 'from/to' scenario, where ordering is important, you need to add one or more columns to the source data to allow the results to appear in the...

The filenames are named with the groups there are in - Filename1_Group1.csv, Filename2_Group1.CSV, Filename3_Group1.csv; Filename1_Group2.csv, Filename2_Group2.CSV, Filename3_Group2.csv, and so on

When I create the dataflow task, I add the name...

Good stuff and totally understood.  The only question I have now is how do decide which group any given file is in?  I don't know of a reason why...

What is the problem? Can you be more specific?

I can't see any easy way of accommodating scenario four in a MERGE, because MERGE does not allow an INSERT in the case of a match.

You could experiment with changing...

No.

CASE is an expression, which returns a result....

The query 'brings all the data' because you are not filtering any of it out. If you wish to filter out data, use a WHERE clause.

I asked you about TrnYear,...

As an aside, as you're using SS 2019, I suggest you start using STRING_AGG() rather than the legacy FOR XML PATH method.

Makes your code much easier to read.

OK, so now you understand why you did not get 'NOTPRIMARY', I assume?

Well spotted, thanks for fixing that up.

If you run just the inside part of the query, as follows, what result do you get:

SELECT CASE
         ...

Can we recap on exactly what the problem...

Try this:

CREATE TABLE T
(
    title VARCHAR(100) NOT NULL
   ,Computed AS
        REPLACE(
    ...

By the way, your query is far easier to read if it is formatted and presented inside a code block, like this:

SELECT DISTINCT
    ...