STUFF FOR XML Concat is truncating the rest of the records

Question

I have an accounts table that is used for adding columns as pivoted to a view.

Account
Account1
Account2
Account3
Account4
Account5
Account6
Account7
Account8

Currently I do this process manually, where anytime there are new accounts added, I drop and recreate the view and add the new accounts as columns manually.

To automate this process, I'm creating a dynamic sql stored procedure and as part of the procedure, I have the following query:

SELECT @pivotColumns = STUFF(
   (SELECT DISTINCT concat(',', quotename(AccTbl.Account))
    FROM [Accounts] AccTbl
    WHERE AccTbl.Active = 'YES'
    FOR XML Path(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 2, '');

For some reason, the resulting dynamic sql result has the remaining accounts truncated, it only prints the first few accounts out of the 40+ there are in the accounts table...why is that?

[Account1],[Account2],[Account3],[Account4],[Account5],[Account6

Is it because of this part?

, 1, 2, '')

Note: I have over 40+ accounts. For simplicity purposes, I am only representing about 8 here. Note2: I know folks will recommend STRING_AGG but I have sql server 2016 at the moment :/

Answer 1

Change

, 1, 2, '')

To

, 1, 1, '')