0

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 :/

Cataster
  • 3,081
  • 5
  • 32
  • 79
  • Give us a [mre]. What you have works fine (apart from it removes the first bracket (`[`), but that is what you've asked it to do). [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e1e71b4b1c7ee2e07674a530a0961ae2) – Thom A Feb 16 '21 at 22:27
  • The part you're indicating `, 1, 2, '')`, is the end of the call to the `STUFF()` function. All that function is doing is deleting the initial comma. – Bacon Bits Feb 16 '21 at 22:34
  • 1
    Are you running into the SSMS limitation? https://stackoverflow.com/questions/3113360/for-xml-length-limitation – Bacon Bits Feb 16 '21 at 22:34
  • *And* the first bracket, @BaconBits . The OP specifies they want to remove the first 2 characters, which would be `',['`. – Thom A Feb 16 '21 at 22:34
  • @BaconBits youre right!! I am running into SSMS limitation!! man it escaped my mind that could be the case – Cataster Feb 16 '21 at 22:37
  • @Larnu actually i want the resulting columns to be with brackets, comma separated. so expected output is something like `[Account1],[Account2],[Account3],[Account4]`. on a side note, how do i create new lines between each attribute? instead of all of them being on one line, i'd like to format it so that the DDL would look nicer, e.g. `[Account1],*NEWLINEHERE*[Account2],*NEWLINEHERE*[Account3],*NEWLINEHERE*[Account4]` – Cataster Feb 16 '21 at 22:41
  • 1
    `CHAR(13)` (Carriage Return) and `CHAR(10)` (Line Break) would add new lines. – Thom A Feb 16 '21 at 22:44
  • @Larnu interesting. whats the difference? – Cataster Feb 16 '21 at 22:46
  • 1
    What do you mean, what's the difference? Difference between what? – Thom A Feb 16 '21 at 22:47
  • @Larnu meant between the carriage return and return. I did some research and testing I was curious lol. Looks like theres not much difference except formatting may be different in various tools – Cataster Feb 17 '21 at 15:45
  • 1
    The short (and incomplete) answer is Line Break is used in Unix based operating systems, and Carriage Return + Line Break by Windows based operating systems. – Thom A Feb 17 '21 at 15:46
  • @Larnu been having internet issue due to the storm lately, apologize for getting late back to you! Really appreciate your help! – Cataster Feb 17 '21 at 15:51

1 Answers1

1

Change

, 1, 2, '')

To

, 1, 1, '')
SQLpro
  • 3,994
  • 1
  • 6
  • 14
  • This slightly helped with parsing but the actual answer to this question is this is an SSMS limitation. Could you update your answer with this info? https://stackoverflow.com/questions/3113360/for-xml-length-limitation – Cataster Feb 17 '21 at 15:33