0

I am writing the following dynamic SQL, and getting an error in the PIVOT section of the View creation.

Incorrect syntax near ','

FOR [Accounts] IN (
                ' + @pivotColumns + '

I know why, its because there shouldn't be a comma that precedes the FIRST @pivotColumn (see image below for example). However, the column is a result of dynamic SQL, how do I go about resolving this? And I cant use trailing comma instead because then I will have a similar problem with the last column.

Basically, I need a way to make

SELECT @pivotColumns =....

not append a leading comma to the FIRST column/Account selected.

The comma is added at the end at this part of @pivotColumns:

',', quotename

Full Query:

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

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

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:

example

Cataster
  • 3,081
  • 5
  • 32
  • 79
  • You have `CHAR(9)` (a tab) **and** `','` at the start of your string. `STUFF` should be removing the first 2 characters, not the first one. – Thom A Mar 31 '21 at 21:52
  • @Larnu I just removed `CHAR(9)` and youre right! if im understanding you correctly, a tab is multiple characters, so technically its just removing spaces from the tab, but i dont understand...so `STUFF` removes 1st 2 characters ONLY from the 1st column? i guess what im confused about is how arent the other columns affected? in other words, how come the leading commas for Costs and NET INCOME are not truncated? – Cataster Mar 31 '21 at 21:56
  • No, `STUFF` removes (technically replaces) however many characters you tell it to. You told it to remove 1 and it did; the tab. That leaves the comma in place. – Thom A Mar 31 '21 at 22:03
  • @Larnu but it removed the leading comma for Expenses...which resolved my issue so great but its illogical – Cataster Mar 31 '21 at 22:07
  • 1
    I actually would suggest it didn't. You image of code says you have `[Style Code]\n{tabs},Expenses` but in your code above it's `[Style Code],{Dynamic Columns}` Notice that the comma has moved from after the line break to before it. That image isn't of the above generated SQL. BUt, again, the reason it's not working is because you aren't removing the comma, just the tab. Again, this is why I mentioned you need to understand what `STUFF`, and `FOR XML PATH` is doing. `STUFF({expression},1,1,'')` will remove the **first** character only. `STUFF({expression},1,2,'')` would remove the 1st 2. – Thom A Apr 01 '21 at 07:54
  • @Larnu yes youre right, the image isnt representative of the generated SQL mainly because i had to edit it in paint quickly to demonstrate the comma issue. but i realize the tabs is very important in representing the problem at core now. my mistake. I completely understand now, thanks for your help! should i delete this post? also a little off topic but ive been getting a popup message on SO saying `"STOP! You have one free copy/paste remaining. Get unlimited copy/pasting with The Key by Stack Overflow today."` is that a scam or something? – Cataster Apr 01 '21 at 16:29
  • [Announcing “The Key™” - copy paste like you've never done before](https://meta.stackoverflow.com/questions/406398/announcing-the-key-copy-paste-like-youve-never-done-before?cb=1). Also, notice the date. ;) – Thom A Apr 01 '21 at 16:44
  • @Larnu I CANT BELIEVE I FELL FOR IT! WOOOOOOWWWWW – Cataster Apr 01 '21 at 17:18

0 Answers0