Strip the last dynamic column from ending comma?

Question

I am writing the following dynamic SQL, and getting an error around the FROM keyword.

Incorrect syntax near the keyword 'FROM'.

  ' + @columnList + '
FROM [History] 

I know why, its because there shouldn't be a comma that precedes it. However, since the column before it (@columnList) is a result of dynamic SQL, how do I go about resolving this?

Basically, I need a way to make

SELECT @columnList =....

not append a comma at the end to the LAST column/Account selected.

The comma is added at the end at this part:

quotename(AccTbl.Account), ',',

Full Query:

DECLARE @sqlCommand NVARCHAR(MAX) = '',
        @columnList NVARCHAR(MAX) = '',
        @pivotColumns NVARCHAR(MAX) = '';

SELECT @columnList =
  (SELECT DISTINCT concat(CHAR(9), 'COALESCE(', quotename(AccTbl.Account), ', 0)', quotename(AccTbl.Account), ',', CHAR(10)) --CHAR(9) & CHAR(10) for indentation/formatting
   FROM [Accounts] AccTbl
   WHERE AccTbl.Account NOT IN ('WS')
   FOR XML Path(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)');

SELECT @pivotColumns = STUFF(
   (SELECT DISTINCT concat(CHAR(9), ',', quotename(AccTbl.Account), CHAR(10))
    FROM [Accounts] AccTbl
    WHERE AccTbl.Account NOT IN ('WS')
    FOR XML Path(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 1, '');

/*
EXEC the sqlCommand as separate batches to prevent this error: 'CREATE VIEW' must be the first statement in a query batch.
https://stackoverflow.com/a/39135516/8397835
*/

SET @sqlCommand = '
    USE [ABC_DB]
    --GO
    DROP VIEW IF EXISTS [dbo].[Piv];
    --GO
    SET ANSI_NULLS ON
    --GO
    SET QUOTED_IDENTIFIER ON
    --GO
';

Execute sp_executesql @sqlCommand;

SET @sqlCommand = '
    CREATE VIEW [dbo].[Piv]
    AS
    (SELECT
      [Style Code],
      ' + @columnList + '
    FROM [History] 
        PIVOT (SUM([Value]) FOR [Accounts] IN (
            ' + @pivotColumns + '
            )
        ) 
    AS Piv);
';

PRINT @sqlCommand;
Execute sp_executesql @sqlCommand;

In other words, whats happening right now is something like this:

UPDATE:

@columnList was fixed with leading comma instead of trailing comma, but leading comma nor trailing comma would work for @pivotColumns because we don't have a pre-existing column in the PIVOT part of the query like we do in the SELECT statement with Style Code.

Answer 1

Don't put the commas at the end, put them at the start, and then strip the first character, using STUFF, that's far easier in T-SQL.

So instead of {Expression} + ',' do ',' + {Expression}. Then you can simply do STUFF(@columnList,1,1,'') to remove the leading comma instead.

Answer 2

Part of the problem is your 'formatting' of the code. Generally I would agree that formatting the dynamic code can help in debugging - here it is getting in the way.

SELECT @columnList = STUFF(
  (SELECT DISTINCT concat(', ', quotename(AccTbl.Account))
   FROM [Accounts] AccTbl
   WHERE AccTbl.Account NOT IN ('WS')
   FOR XML Path(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 1, '');

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

Removing those - we see that both statements are exactly the same. Assume the results from the Accounts table are: Acct1, Acct2

You would get the result as '[Acct1], [Acct2]' for both @columnList and @pivotColumns. So - if you want to expand on the column list portion, for example add the table alias (which is what I would do):

SELECT @columnList = STUFF(
  (SELECT DISTINCT concat(', ', 'h.', quotename(AccTbl.Account))
   FROM [Accounts] AccTbl
   WHERE AccTbl.Account NOT IN ('WS')
   FOR XML Path(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 1, '');

I would not bother with formatting these columns in the final result, since you will be recreating the view as needed using dynamic SQL.

SET @sqlCommand = '
    CREATE VIEW [dbo].[Piv]
    AS
    (SELECT
      h.[Style Code],
      ' + @columnList + '
    FROM [History] h
        PIVOT (SUM(h.[Value]) FOR [Accounts] IN (
            ' + @pivotColumns + '
            )
        ) 
    AS Piv);
';