Adding vertical sum of rows(Sum in last availble column) for Transposed data Slight modification due to rows data increased limits

Transact-SQL
1

Hi Experts!
Due to limitation of Sqlserver Cannot create a row of size 10443 which is greater than the allowable maximum row size of 8060. I required your kind help to re-modify the script to minimize the rows to match the sqlserver limitation. Below link post worked for me like a charm . This happening because of my data so long.
The data results currently retrieving like below screenshot.

image
image1406×118 122 KB

Help me to change to minimize the rows based on Itemname can we concatenate the ItemNames with Data header like ItemName1-Data1 , ItemName-Data2 to reduce the ItemName columns?

https://forums.sqlteam.com/t/adding-vertical-sum-of-rows-sum-in-last-availble-column-for-transposed-data/20382

2

Simple addition should do the trick for you.

3

Sir JeffModen, Thank you please have a look...

4

Are you saying that you're trying to save the output to another table and that's when you get the row-size error? Or is it erroring even when it tries to display to the grid? And are you trying to get it into a spreadsheet?

If the latter, perhaps you simply get the Date, ItemName, Data1, and the Data2 columns for each Date/Item name, drop THAT into a spreadsheet, and then use the PIVOT functionality in the spreadsheet (I don't know what the limits are there, though).

5

Sir the error thrown while running the Procedure directly on SSMS.

image
image850×351 24.5 KB

6

I get that. But you posted some results in a spreadsheet and I asked you a question about the spreadsheet. Is that the ultimate target of the data?

As for the error, I've not run into that issue before. It may be a hard stop.
How many ItemNames are you using?

7

Sir,
I have ItemNames currently till today 419 Items*3( ItemNames,Data1 and Data2) = 1257 so might be this is not a issue for through an error.

I just re tried to run the test data not throw any error retrieve the rows in post https://forums.sqlteam.com/t/adding-vertical-sum-of-rows-sum-in-last-availble-column-for-transposed-data/20382

image
image851×394 24.4 KB

But when i try to run the new Script with Totals find error required your suggestion?

The excel results are based on your previous sent script which worked very fine and excel spreadsheet screenshot attached desire results,since January now might be the naming convention of ItemName through error..