SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Add grand total at the bottom of SQL pivot table?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 11/09/2013 :  07:12:19  Show Profile  Reply with Quote
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

547 Posts

Posted - 11/09/2013 :  08:51:30  Show Profile  Reply with Quote
Here is one way:


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



Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 11/09/2013 :  10:13:25  Show Profile  Reply with Quote
Thanks for that, it works perfectly in the example you've provided but I'm struggling to adapt your code to fit my requirements?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/09/2013 :  10:38:13  Show Profile  Reply with Quote
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
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 11/09/2013 :  11:34:17  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/10/2013 :  01:46:36  Show Profile  Reply with Quote
can you explain how you got those numbers changed?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 11/10/2013 :  02:09:57  Show Profile  Reply with Quote
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

Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 11/10/2013 :  14:39:30  Show Profile  Reply with Quote
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?



Edited by - Rasta Pickles on 11/19/2013 14:45:39
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000