By using GROUPING SETS, you can add your total rows to your subquery, as a simple example if you have a query:
SELECT A, B, SUM(C) AS C
FROM T
GROUP BY A, B;
That gives you:
A B C
-------------------
1 1 5
1 2 3
2 1 8
2 2 1
If you use grouping sets as follows
SELECT A, B, SUM(C) AS C
FROM T
GROUP BY GROUPING SETS ((A, B), (A));
You get
A B C
-------------------
1 1 5
1 2 3
1 NULL 8 -- Total for A = 1
2 1 8
2 2 1
2 NULL 9 -- Total for A = 2
This is the equivalent of:
SELECT A, B, SUM(C) AS C
FROM T
GROUP BY A, B
UNION ALL
SELECT A, NULL, SUM(C) AS C
FROM T
GROUP BY A;
So each Grouping set essentially represents a further query, but internally SQL Server is able to re-use the aggregates, so is more efficient. All you need to do then is replace the NULL values for Total, and you have your total row(s).
I would also advise against variable concatenation (SELECT @Columnname = @ColumnName + SomeField FROM SomeTable) since the results are not guaranteeed to be correct. Instead use XML extensions to concatenate your rows to columns.
In addition, I would use a parameterised query, so rather than:
DECLARE @Variable VARCHAR(10) = 'TEST';
SET @DynamicPivotQuery = 'SELECT * FROM T WHERE Column = ''' + @Variable + '''';
EXECUTE sp_executesql @DynamicPivotQuery;
Instead use:
DECLARE @Variable VARCHAR(10) = 'TEST';
SET @DynamicPivotQuery = 'SELECT * FROM T WHERE Column = @Param';
EXECUTE sp_executesql @DynamicPivotQuery, N'@Param VARCHAR(10)', @Param = @Variable;
This gives you properly typed parameters, so there is no need to convert your dates to varchars to add them to your query, only for your query to have to convert them back to dates when it executes.
Finally, I haven't corrected this, but I would reccomend agaainst using BETWEEN when working with dates, the reasons for this are summed up nicely in the following articles:
Which gives you a final query of: '
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @start AS DATETIME
DECLARE @end AS DATETIME
DECLARE @business AS VARCHAR(50)
SET @start = '2015-01-01';
SET @end = '2015-12-01';
SET @business = 'EUR';
--Get distinct values of the PIVOT Column
-- Uses "DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date]), 0)" to get the first of each
-- month then converts this to the format "yyyymmdd" (this is culture insensitive)
SET @ColumnName =
STUFF(( SELECT ',' + QUOTENAME(CONVERT(VARCHAR(10), D.[Date], 112))
FROM ( SELECT [Date] = DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date]), 0)
FROM bus_best
WHERE [Date] BETWEEN @start AND @end
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date]), 0)
) AS d
ORDER BY d.[Date]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
SET @DynamicPivotQuery =
'SELECT Name, ' + @ColumnName + '
FROM ( SELECT SUM(bb.value) AS Value,
Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, bb.date), 0),
ISNULL(c.name, ''Total'') AS Name
FROM bus_best bb
INNER JOIN pro AS p ON p.id = bb.id
INNER JOIN con AS c ON c.id = p.id
INNER JOIN bus_t AS bu ON bu.id = c.id
WHERE bb.date BETWEEN @StartParam AND @EndParam
AND bu.name = @BusinessParam
GROUP BY GROUPING SETS
( (DATEADD(MONTH, DATEDIFF(MONTH, 0, bb.date), 0), c.name),
(DATEADD(MONTH, DATEDIFF(MONTH, 0, bb.date), 0))
)
) AS t
PIVOT
( SUM(t.value)
FOR date IN (' + @ColumnName + ')
) AS PVTTable;';
EXECUTE sp_executesql
@DynamicPivotQuery,
N'@StartParam DATETIME, @EndParam DATETIME, @BusinessParam VARCHAR(50)',
@StartParam = @Start,
@EndParam = @End,
@BusinessParam = @Business;
N.B. I have not tested this fully, since it would require creating 4 tables which I can only guess at the data for, but there is hopefully enough information in the answer and the links to get you on the right track if there are some minor syntax errors
FULL WORKING EXAMPLE
IF OBJECT_ID(N'tempdb..#T', 'U') IS NOT NULL DROP TABLE #T;
CREATE TABLE #T
(
[Date] DATE,
Business VARCHAR(50),
Value INT,
Name VARCHAR(50)
);
INSERT #T (Date, Business, Value, Name)
VALUES
('20150601', 'EUR', 1, 'Group 1'),
('20150605', 'EUR', 12, 'Group 2'),
('20150605', 'EUR', 3, 'Group 3'),
('20150701', 'EUR', 2, 'Group 1'),
('20150708', 'EUR', 2, 'Group 2'),
('20150702', 'EUR', 7, 'Group 3'),
('20150703', 'AAA', 2, 'Group 1');
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @start AS DATETIME
DECLARE @end AS DATETIME
DECLARE @business AS VARCHAR(50)
SET @start = '2015-01-01';
SET @end = '2015-12-01';
SET @business = 'EUR';
--Get distinct values of the PIVOT Column
SET @ColumnName =
STUFF(( SELECT ',' + QUOTENAME(CONVERT(VARCHAR(10), D.[Date], 120))
FROM ( SELECT [Date] = DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date]), 0)
FROM #T
WHERE [Date] BETWEEN @start AND @end
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date]), 0)
) AS d
ORDER BY d.[Date]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
SET @DynamicPivotQuery =
'SELECT Name, ' + @ColumnName + '
FROM ( SELECT SUM(bb.value) AS Value,
Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, bb.date), 0),
ISNULL(bb.name, ''Total'') AS Name
FROM #T AS bb
WHERE bb.date BETWEEN @StartParam AND @EndParam
AND bb.Business = @BusinessParam
GROUP BY GROUPING SETS ((bb.date, bb.name), (bb.Date))
) AS t
PIVOT
( SUM(t.value)
FOR date IN (' + @ColumnName + ')
) AS PVTTable;';
EXECUTE sp_executesql
@DynamicPivotQuery,
N'@StartParam DATETIME, @EndParam DATETIME, @BusinessParam VARCHAR(50)',
@StartParam = @Start,
@EndParam = @End,
@BusinessParam = @Business;
