Firstly you need to fix the code you are using to create the tables - currently it produces these errors
Msg 173, Level 15, State 1, Line 3
The definition for column 'prdqtyint' must include a data type.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'insert'.
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near ','.
Once you fixed those you will then get a further error
Msg 213, Level 16, State 1, Line 13
Column name or number of supplied values does not match table definition.
Because the values you are inserting into
#ItemMasterFile
do not match the definition of the table. You've also done that with
#Probale
where you are passing
varchar
values into
int
columns but SQL has converted that for you.
I commend you for giving us code to recreate your data tables, but for future reference your lack of care on this will make many members just give up.
The problem persists when I start to examine your dynamic query -
Msg 207, Level 16, State 1, Line 78
Invalid column name 'DelID'.
I just deleted that from my version.
Incidentally, I also dislike the inconsistency of
exec sp_executesql @query , N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate
EntryDate is a
date
so why start using
datetime
. So I finally got your results of
Descriptionitem 1 2 3 4 5
A 1 NULL 1 NULL NULL
B 1 1 NULL 1 NULL
C 1 1 NULL NULL NULL
D NULL 1 NULL NULL 1
This is where @OriginalGriff's comments come into play. You haven't made any effort to do this for yourself. So …
To get the row total you will need to add all of the columns together. Use the same technique you used to get
@cols
but note that you will have to deal with those NULL values - I suggest using something like
SELECT Descriptionitem, ISNULL([1],0) AS [1], … etc
To get the column totals you will need to
UNION
a query that just totals the columns. I put your pivot into a Common Table Expression to make this easier to do and added
UNION
SELECT 'Total', SUM([1]), SUM([2]),SUM([3]),sum([4]),sum([5]), SUM(([1] + [2] + [3] + [4] + [5])) AS RowTotal
from CTE
Note I don't have to worry about ISNULL because SUM will handle null values for me.
But also note that I had to include the sum of the rowtotals to make the union work