I have a dynamic Pivot query which looks like:
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
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(date1)
FROM (
SELECT m.date1, m.date2 FROM(
SELECT DISTINCT CONVERT(nvarchar(50), DATENAME(m, date)
+ ', '
+ DATENAME(yyyy,date)) as date1, date as date2
FROM bus_best where date between @start and @end
)m
)tab order by tab.date2
SET @DynamicPivotQuery =
'select * from (
select sum(bb.value) as value, bb.date as date, c.name as Name from bus_best bb
join pro p on p.id = bb.id
join con c on c.id = p.id
join bus_t bu on bu.id = c.id
where bb.date between '''+ cast (@start as VARCHAR(50))+''' and '''+ cast (@end as VARCHAR(50))+'''
and bu.name = '''+ cast (@business as VARCHAR(50))+'''
group by bb.date, c.name
) as t
PIVOT(SUM(t.value)
FOR date IN (' + @ColumnName + ')) AS PVTTable'
EXEC sp_executesql @DynamicPivotQuery
and the output is something like:
Name Jan Feb March April May June July ....
----------------------------------------------------------
Name1 32 654 1 42 342 4 4543
Name2 54 3 234 43 453 432 22
Name3 55 12 56 1234 43 643 12
Name4 77 235 3566 35635 23 2 3462
All I want is to add at bottom the last row which will sum all rows something like:
Name Jan Feb March April May June July ....
----------------------------------------------------------
Name1 32 654 1 42 342 4 4543
Name2 54 3 234 43 453 432 22
Name3 55 12 56 1234 43 643 12
Name4 77 235 3566 35635 23 2 3462
Total ... .... .... .... .... .... .....
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;