Add grand total at the bottom of SQL pivot table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Add grand total at the bottom of SQL pivot table?

Author

Topic

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-11-09 : 07:12:19

Banging my head against a brick wall with this :-(

I've been messing around with ROLLUP but have got nowhere, hope someone can help!

I have this:


DECLARE @sql NVARCHAR(MAX)
DECLARE @pivotsql VARCHAR(MAX)
DECLARE @columns VARCHAR(MAX)
DECLARE @selectlist VARCHAR(MAX)

SET @columns = N'';
SELECT @columns += N', c.' + QUOTENAME(urn)
FROM (SELECT c.urn FROM Cashbook.MappingMenu AS c
LEFT JOIN Cashbook.CashDetail AS m ON m.MappingMenu = c.urn
GROUP BY c.urn, m.MappingMenu) AS x;

SET @selectlist = N'';
SELECT @selectlist += N', ISNULL(' + QUOTENAME(urn) + ', 0) AS ' +
QUOTENAME(urn) FROM (SELECT c.urn FROM Cashbook.MappingMenu AS c
LEFT JOIN Cashbook.CashDetail AS m ON m.MappingMenu = c.urn
GROUP BY c.urn, m.MappingMenu) AS x;

SET @selectlist = STUFF(@selectlist, 1, 1, '');
SET @pivotsql = N'SELECT CONVERT (VARCHAR, CreatedOn, 103) AS Date,  ' + @selectlist + '
FROM (SELECT CreatedOn, MappingMenu, Value FROM Cashbook.CashDetail AS m) AS j

PIVOT

(SUM(Value) FOR MappingMenu IN ('+ STUFF(REPLACE(@columns, ', c.[', ',['), 1, 1, '') + ')) AS c';

EXEC (@pivotsql)

which works a treat - I just need a grand total at the bottom for each column.

Many thanks for any assistance.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-11-09 : 08:51:30

Here is one way:
[CODE]

DECLARE @Sample TABLE
(
Prod_ID VARCHAR(10) NOT NULL,
Category INT NOT NULL,
Price MONEY NOT NULL
);

INSERT @Sample
(
Prod_ID,
Category,
Price
)
VALUES ('0000001', 102, 164.9000),
('0000001', 103, 164.9000),
('0000001', 106, 161.8000),
('0000003', 100, 164.9000),
('0000003', 102, 164.9000),
('0000003', 103, 164.9000),
('0000003', 106, 161.8000),
('0000007', 100, 164.9000),
('0000007', 102, 164.9000),
('0000007', 103, 164.9000),
('0000007', 106, 161.8000),
('0000008', 100, 164.9000),
('0000008', 102, 164.9000),
('0000008', 103, 164.9000),
('0000008', 106, 161.8000);

WITH CTE AS
(SELECT p.Prod_ID,
p.[100] AS Group100,
p.[102] AS Group102,
p.[103] AS Group103,
p.[106] AS Group106
FROM @Sample AS s
PIVOT (
MAX(s.Price)
FOR s.Category IN ([100], [102], [103], [106])
) AS p)
SELECT * from CTE
UNION
SELECT 'Grand Total' as Prod_ID, SUM(Group100) as Group100, SUM(Group102) as Group102,
SUM(Group103) as Group103, SUM(Group106) as Group106 from CTE

[/CODE]

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-11-09 : 10:13:25

Thanks for that, it works perfectly in the example you've provided but I'm struggling to adapt your code to fit my requirements?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-09 : 10:38:13

quote:
Originally posted by Rasta Pickles

Thanks for that, it works perfectly in the example you've provided but I'm struggling to adapt your code to fit my requirements?

then show sample data to depict your exact requirement for us to help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-11-09 : 11:34:17

The query above generates this sample data:


Date	        1       2       3       4       5                    
01/11/2013	50.00	29.00	0.00	0.00	0.00		
09/11/2013	0.00	71.00	0.00	0.00	0.00

I'm looking to achieve:


Date	        1       2       3       4       5                    
01/11/2013	50.00	29.00	0.00	0.00	0.00		
09/11/2013	0.00	61.00	0.00	0.00	0.00
TOTAL           50.00   90.00   0.00    0.00    0.00

Thank you for reading.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-10 : 01:46:36

can you explain how you got those numbers changed?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-11-10 : 02:09:57

Apologies, I changed the source data whilst experimenting :-(

Before and after values:


Date	        1       2       3       4       5                    
01/11/2013	50.00	29.00	0.00	0.00	0.00		
09/11/2013	0.00	61.00	0.00	0.00	0.00


Date	        1       2       3       4       5                    
01/11/2013	50.00	29.00	0.00	0.00	0.00		
09/11/2013	0.00	61.00	0.00	0.00	0.00
TOTAL           50.00   90.00   0.00    0.00    0.00

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-11-10 : 14:39:30

Close this topic off, as a novice I've managed to sort it for myself.

Hey ho, we're all on a learning curve eh?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources